Search code examples
perldatecsvxlsx

Convert serial value to date in perl

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

Solution

  • 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.