Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Spreadsheet::ParseExcel and Dates

by sabersd (Initiate)
on Oct 09, 2006 at 23:35 UTC ( #577287=perlquestion: print w/replies, xml ) Need Help??
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}));

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

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

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}); }
    See Spreadsheet::ParseExcel::Utility for details.
Re: Spreadsheet::ParseExcel and Dates
by bobf (Monsignor) on Oct 10, 2006 at 04:21 UTC

      you could probably modify fairly easily to do the opposite conversion ... If you do, please post the code.

      See: DateTime::Format::Excel


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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://577287]
Approved by chargrill
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (8)
As of 2018-03-20 21:49 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (259 votes). Check out past polls.