ParseExcel date fromat 1

[MakAtak]

Hi all,

I have managed to take an excel spreadsheet and re-format the file as a delimited file using ParseExcel.
The problem I have is that the dates in the input file are being re-formatted in the output file.

The dates in the input file are in this format: 01/06/1976

When I use "print $myCell->Value", the date appears in the output file as 10-1-96.

If I use "print $myCell->{Val}", the date appears as 27912.

How do I get the output date to be the same format as the input date?

 

[raklet]

Try the FAQ for parsing dates with this module:

http://www.cpanforum.com/posts/363

 

[MakAtak]

raklet,

thanks for pointing me in the direction of this thread.

The bad news is that the input file is supplied by a client, which has to be re-formatted into a flat text file for further processing.

To get them to change the date to a format that can be handled is a non-starter, as we originally requested the data in csv format and this was refused.
Hence using the ParseExcel module (which I have to add is excellent).

 

[raklet]

If I use "print $myCell->{Val}", the date appears as 27912.

It appears that this number (27912) is the numbers of days since Jan 1, 1900. (Jan 1, 1904 if on a Mac) So, using Date::Calc, it is possible to convert this number of days back into a human readable date.

use strict;
use Date::Calc qw(Add_Delta_Days);
my ($year,$month,$day) = Add_Delta_Days(1899,12,30, 27912);
print "$year $month $day";

Format of this function is:

Add_Delta_Days
------------------
($year,$month,$day) = Add_Delta_Days($year,$month,$day, $Dd);

Plug in the starting year, month, day, and the number of days since that time period. You may have to play around with the starting y,m,d to get the date right.

Hth,

Raklet

 

[brigmar]

Try using the date::calc module.

http://guest.engelschall.com/~sb/download/Date-Calc/

CPAN reference:

http://search.cpan.org/~stbey/Date-Calc-5.4/Calc.pod

If I use "print $myCell->{Val}", the date appears as 27912.

That 27912 is the number of days since 30-Dec-1899 (it is how excel stores dates).

You should be able to create a date object, add the excel number (27912), and extract the day/month/year info from that object for your output.

 

[MakAtak]

Thank you both for your help with this one, that works a treat!
Have a star raklet!