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";

                  
                }        

                }
            }        
    }
    ####

[download]
--------
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]

[download]

In reply to Convert between DateTime and Excel dates by madtoperl

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.