Convert between DateTime and Excel dates

[madtoperl]

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]

[1Nf3]

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

[madtoperl]

Thanks Luke. It worked well for me.

[Corion]

Also see Spreadsheet::ParseExcel::Utility, which has (different?) conversion routines for conversion between Excel and epoch times.