Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Convert between DateTime and Excel dates

by madtoperl (Monk)
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"; } } } } ####
--------
output:
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]

Comment on Convert between DateTime and Excel dates
Select or Download Code
Re: Convert between DateTime and Excel dates
by Corion (Pope) on Jan 12, 2009 at 12:31 UTC
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";

    Regards,
    Luke

      Thanks Luke. It worked well for me.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://735654]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (14)
As of 2014-09-18 19:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (123 votes), past polls