Convert serial value to date in perl

Question

I am using Spreadsheet::XLSX to convert XLSX into CSV on Linux. Custom date fields are being converted to numbers. I know that XLSX stores custom dates as serial values. I need to find the way to convert those values into dates/times.

Example:

CSV:  40829
XLSX: 10/13/2011 0:00

So I am trying to figure out how to convert 40829 to 10/13/2011 0:00

I did some research and I was not able to find any (Perl) solution. I can provide the code if needed.

Please advise.

Thank you, -Andrey

Answer 1

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt.

You could write a function to do the calculations yourself or you could look at some of the modules already posted on cpan for doing this, DateTime::Format::Excel should do what you need and DateTimeX::Format::Excel looks like it would work too.

Answer 2

As per the previous post, this seems like it's the number of days since Jan 1st 1900. By making the 40828'th of January, 1900 (accounting for the off-by-one of being the 1st of January, not the 0th), we get:

use POSIX 'mktime'

my $epoch = mktime 0,0,0, 40829-1,0,0;

print scalar localtime($epoch);

Gives

Thu Oct 13 00:00:00 2011