We have an Excel file called dates.xls that if you open you will see this:

The file itself was created using Open Office running on Mac OSX, so it might not be 100% the format a real MS Excel would create, but I hope this won't change the results. In any case, a lot can depend on the exact version of Excel file format, so you might need to tweak the code to fit your files.

Requirements Installation

If you run the code and get an error that starts like this:

Can't locate Spreadsheet/Read.pm in @INC (you may need to install the Spreadsheet::Read module) (@INC contains:

then you need to install Spreadsheet::Read.

If you get an error like this:

Parser for XLS is not installed at

then you also need to install Spreadsheet-ParseXLSX though Spreadsheet-ParseExcel might be enough. It depends on the version of your Excel file.

Spreadsheet::Read

In the first example we use Spreadsheet::Read that makes the code reading the values very simple:

examples/read_excel_dates.pl

#!/usr/bin/perl
use strict;
use warnings;
use 5.010;

my $file = shift or die "Usage: $0 FILENAME\n";

use Spreadsheet::Read qw(ReadData);

my $book = ReadData($file);

say 'A1: ' . $book->[1]{A1};
say 'A2: ' . $book->[1]{A2};
say 'A3: ' . $book->[1]{A3};
say 'A4: ' . $book->[1]{A4};

The output when using this script with the above Excel file:

A1: 05/14/48
A2: 05/14/1948
A3: Friday, May 14, 1948
A4: 14.05.1948

Spreadsheet::ParseExcel

In the second example we used Spreadsheet::ParseExcel for a more detailed reading.

examples/parse_excel_dates.pl

#!/usr/bin/perl
use strict;
use warnings;
use 5.010;
use Spreadsheet::ParseExcel;
use DateTime;

my $file = shift or die "Usage: $0 FILENAME\n";

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse($file);
if ( not defined $workbook ) {
    die $parser->error(), ".\n";
}

my @worksheets = $workbook->worksheets();
say 'A1: ' . $worksheets[0]->get_cell( 0, 0 )->value;
say 'A2: ' . $worksheets[0]->get_cell( 1, 0 )->value;
say 'A3: ' . $worksheets[0]->get_cell( 2, 0 )->value;
say 'A4: ' . $worksheets[0]->get_cell( 3, 0 )->value;
say '';

say 'A1: ' . $worksheets[0]->get_cell( 0, 0 )->unformatted;
say 'A2: ' . $worksheets[0]->get_cell( 1, 0 )->unformatted;
say 'A3: ' . $worksheets[0]->get_cell( 2, 0 )->unformatted;
say 'A4: ' . $worksheets[0]->get_cell( 3, 0 )->unformatted;

say '';

# December 31, 1899
my $start_date = DateTime->new(
    year       => 1899,
    month      => 12,
    day        => 30,
    hour       => 0,
    minute     => 0,
    second     => 0,
);

say "--- Use the date from a cell ---";
say $start_date->ymd;
my $date = $start_date->clone->add( days => $worksheets[0]->get_cell( 0, 0 )->unformatted );
say $date->ymd;

The output when using this script with the above Excel file:

A1: 05/14/48
A2: 05/14/1948
A3: Friday, May 14, 1948
A4: 14.05.1948

A1: 17667
A2: 17667
A3: 17667
A4: 14.05.1948

--- Use the date from a cell ---
1899-12-30
1948-05-14

See also dealing with dates as data in Excel