MMX has asked for the wisdom of the Perl Monks concerning the following question:

I'm using ParseExcel to extract data from a set of strangeley formatted spreadsheets, and for the most part it works like a charm.

I've run into a strange problem with dates though if ParseExcel parses some of my cells with singel digit month dates as like so strangely:


Excel Cell B5 and B7:
"1/1/2004 7:00:00"
"2/1/2004 7:00:00"

for some reason both end up as:
"M/1/2004 7:00:00 AM/PM"

In method '$cell->Value' which adds complications to my scripts further down the line. The cell does seem to parse correctly partially, as $cell-${Val} yields 37987.29166 which I believe to be correct. What can I do to get around this behavior?

  • Comment on Spreadsheet::ParseExcel and Date strangeness

Replies are listed 'Best First'.
Re: Spreadsheet::ParseExcel and Date strangeness
by jmcnamara (Monsignor) on Jan 07, 2005 at 16:54 UTC

    I couldn't reproduce this error. Could you confirm that the number format for the cells in Excel is as follows: 
        d/m/yyyy hh:mm

    [download]
    Or if not could you post the actual format.

    Also, which version of Excel and Spreadsheet::ParseExcel are you using.

    --
    John.

[reply]
[d/l]
      I'm using Spreadsheet::ParseExcel v0.2603 with ActiveState Perl 5.6, and Excel 2000 (v9.0.2720).

      Edit:
      After some digging I realized I'm actually running ParseExcel 0.2603, not 0.15.
[reply]

        I've retested it with Excel 2000 and S::PE 0.2603 and I get the same (correct) results.

        So it remains to be verified what is the actual number format that had been specified in Excel. Can you post the format reported be Format->Cells->Number->Category in Excel.

        --
        John.

[reply]
Re: Spreadsheet::ParseExcel and Date strangeness
by MMX (Initiate) on Jan 08, 2005 at 02:51 UTC
    Well, using Date::Calc, I sort of put together a workaround for my problem.

    my $OutCell = ref $cell ?
    $cell->Value : '';

# Dealing with Mangled Dates
if ( $OutCell =~ /M\//)
{
    
    
    my($datetime);
    $datetime = $cell->{Val};
    my ($Dd,$Dh,$Dm,$Ds) = ($datetime =~ /(\d+)\.(\d\d)(\d\d)(\d\d)/);
    my ($year,$month,$day, $hour,$min,$sec) =
        Add_Delta_DHMS(1900,1,1, 0,0,0, $Dd,$Dh,$Dm,$Ds);
                    
    $OutCell = $month . "-" . $day . "-" . $year ;
                    
                    
}

    [download]
    This puts the date into a format that can be read elsewhere. Not exactly the best fix, but hey.
[reply]
[d/l]

Back to Seekers of Perl Wisdom