Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Convert between DateTime and Excel dates

by madtoperl (Pilgrim)
on Jan 12, 2009 at 12:27 UTC ( #735654=perlquestion: print w/replies, xml ) Need Help??
madtoperl has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,
I am having a excel file with two colums. one column is having intime of an employee and second column is having outtime of an employee with the format of DD/M/YYYY H:M:S i.e "12/1/2008 9:54:00 AM"
I am trying to extract date and time value from the microsoft excel file using perl.While extracting the value I got the output value of the particular column as 32793.442 instead of "2008:12:1 9:54:00". so I used the DateTime::Format::Excel module and I can able to extract the value of date i.e "2008:12:1".
I am not able to extract the value of time i.e "9:54:00" using DateTime::Format::Excel module. Could you please let me know if any module is available to get the time also. Orelse is there any way to extract the time value?
Orelse is there anyway to find the difference of time between the intime and outime column value? Please help me to solve this issue.
piece of code is avail here,
###Get the intime value foreach my $sheet (@{$workbook->{Worksheet}}) { printf("Sheet Name : %s\n", $sheet->{Name}); foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow} ){ foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) + { if ($sheet->{Cells}[$row][$col]->{Val} eq "GRANTED"){ print"Granted Row=> $row column=> $col\n"; my $inTime = $sheet->{Cells}[$row][0]->{Val}; print"Intime=> $inTime\n"; my $datetime = DateTime::Format::Excel->parse_date +time( $inTime ); #print $datetime->ymd('.'); # '2003.02.28' my $idate = $datetime->ymd('.'); print "Date is [$idate]\n"; } if ($sheet->{Cells}[$row][$col]->{Val} eq "Authorized" +){ print"Authorized Row=> $row column=> $col\n"; my $outTime = $sheet->{Cells}[$row][$col+2]->{Val} +; print"Outtime=> $outTime\n"; my $datetime = DateTime::Format::Excel->parse_date +time( $outTime ); #print $datetime->ymd('.'); # '2003.02.28' my $odate = $datetime->ymd('.'); print "out Date is [$odate]\n"; } } } } ####
Granted Row=> 51 column=> 8 Intime=> 39783.4122916667 Date is [2008.12.01] Authorized Row=> 52 column=> 6 Outtime=> 39783.4125 out Date is [2008.12.01]

Replies are listed 'Best First'.
Re: Convert between DateTime and Excel dates
by 1Nf3 (Pilgrim) on Jan 12, 2009 at 13:04 UTC

    According to the module documentation the following should work:

    use Spreadsheet::ParseExcel::Utility qw(ExcelLocaltime); ... my $inTime = $sheet->{Cells}[$row][0]->{Val}; print"Intime=> $inTime\n"; my ($iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay, $iMSec) = Exce +lLocaltime($inTime); $iYear += 1900; $iMon +=1; print "Date is [$iYear:$iMon:$iDay $iHour:$iMin:$iSec]\n";


      Thanks Luke. It worked well for me.
Re: Convert between DateTime and Excel dates
by Corion (Pope) on Jan 12, 2009 at 12:31 UTC

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (9)
As of 2017-05-23 21:05 GMT
Find Nodes?
    Voting Booth?