Spreadsheet::ParseExcel and Dates

sabersd has asked for the wisdom of the Perl Monks concerning the following question:

It seems that Spreadsheet::ParseExcel isn't working properly with dates, the Excel spreadsheet has 10/01/2006, but when parsed by perl it becomes 38991. But if the dates where entered in as Oct 1 2006, this works fine. I have tried formattting excel, but I get the same results. I am using

my $eff_date = uc(trim($ws->{Cells}[$row][2]->{Val}));
[download]

to get the values from excel, I have also tried {_Value}, but only get the 2 digit year, which will not work.

any help is great, thx

Code tags added by GrandFather

Comment on Spreadsheet::ParseExcel and Dates Download Code

Replies are listed 'Best First'.
Re: Spreadsheet::ParseExcel and Dates by explorer (Chaplain) on Oct 10, 2006 at 00:02 UTC
Maybe your are looking a Date into Excel date format (1/1/1900 day based) 38991 / 365.4 = 106.7	[reply]
Re: Spreadsheet::ParseExcel and Dates by djp (Hermit) on Oct 10, 2006 at 08:02 UTC
Here's some code snippets which will set you on the right track: `use Spreadsheet::ParseExcel::Utility qw(ExcelFmt); ... my $datefmt = 'yyyymmdd'; # ISO 8601 ... if (defined $cell->{Type} && $cell->{Type} eq 'Date') { $value = ExcelFmt($datefmt, $cell->{Val}); }` [download] See Spreadsheet::ParseExcel::Utility for details.	[reply] [d/l]
Re^2: Spreadsheet::ParseExcel and Dates by Anonymous Monk on Apr 22, 2019 at 21:07 UTC
This worked. Thanks!	[reply]
Re: Spreadsheet::ParseExcel and Dates by bobf (Monsignor) on Oct 10, 2006 at 04:21 UTC
Excel stores dates and times as serial numbers that represent an offset from a given date (see Epoch (reference date)) rather than as a string that represents the date or time itself. I wrote a routine to calculate those serial numbers given a date/time string which you could probably modify fairly easily to do the opposite conversion: Re: How to insert local time and date to MS-Access database using PERL program. If you do, please post the code. :-) HTH Update: don't use the code from my other post - use DateTime::Format::Excel. Thanks, jmcnamara.	[reply]
Re^2: Spreadsheet::ParseExcel and Dates by jmcnamara (Monsignor) on Oct 10, 2006 at 09:21 UTC
you could probably modify fairly easily to do the opposite conversion ... If you do, please post the code. See: DateTime::Format::Excel -- John.	[reply]
Re: Problems with Spreedsheet::ParseExcel by runrig (Abbot) on Oct 09, 2006 at 23:59 UTC
Dates are stored in an integer format. Are you using Val or Value to get the value? Val will get the unformatted value (i.e. the integer). Show a bit of code, maybe? Update: When I replied to one of the near duplicate nodes that you posted earlier (and this reply was moved from), you did not list any code, nor did you mention that a 2 digit year was not what you wanted. With that in mind, the replies (now) below are probably more useful. Or you could use Value with the 2 digit year, and possibly add logic to add the century on yourself. And next time, please don't make so many duplicate posts...if you're logged in, you can always update your post (though please mark updates as such so that some of us don't look so stupid answering things that are already answered).	[reply]