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

getting frustratingly murderous at this one ... !

I am analysing a LibreOffice Calc spreadsheet and need to manipulate a date field.

The values are seemingly read in OK but the parsing is not working somehow.

Code replicated below - ignore the positioning of the use statements, just trying to fathom out what is going on/how to get the result that I need and there are several spurious statements present that can probably be/will be redacted, including the print ones that I am using for monitoring purposes. The main If...then...else is still present due to having read in different formats previously (as alluded to) and finding that strptime gave different returns than I was expecting, hence the extra call to ParseDate

Date format used is in UK style dd/mm/YYYY

The date of 31/08/2024 works OK when read in

The data of 01/09/2024 (ie the next calendar day) gives gibberish - with the day coming out as 09 instead of 1, month as 01 instead of 9 and the year as 124.

Note that I have tried specifying the Zone in the ParseDate call but when I set it to GMT, the perl throws up an error about using barewords and using the directive of "ZONE => " does not help

I have tried various parsing calls using different utilities but I can't find one that behaves itself and consistently provides the correct answer or needs the parameters in a specific format (not impossible to do, just seems to be extra, unnecessary work)

I have also tried stepping through the relevant calls (eg strptime) at debugging run time but my perl is not good enough to decipher what is going on with the pattern matching algorithm

Therefore - any recommendations to code alterations/other better-behaved utilities would be gratefully received.

Also - anyone know why ParseDate returns a month one less than it should ?

many thanks

ADB 
 ... code is read into $lCell variable ...
   $lCell .= ""; # Force read detail into a string format, just in cas
+e (as I have seen ... !)

    use Date::Parse;
    use Date::Manip;
    print ("$lCell ...\t");
    my @ttt = strptime ($lCell);
    my $yyy = ParseDate ($lCell,,);
    #print "ADB $yyy...\n";
    if ($yyy ne "")
    {
        # Format originally read was in the format 2024-08-09 before c
+onverting to string format
        #code.
        $lDay = substr($yyy,6,2);
        $lMonth = substr($yyy,4,2);
        $lYear = substr($yyy,0,4);
    }
    else
    {
        # Format was originally 16-08-2024 or similar
        $lDay = $ttt[5];
        # NOTE HERE that the month range in some perl modules has a ra
+nge 0..1 NOT 1..12, so adjust
        $lMonth = $ttt[4] +1;
        $lYear = $ttt[3];
    }

[download]

Replies are listed 'Best First'.
Re: date parsing
by hippo (Archbishop) on Jun 10, 2025 at 21:25 UTC

    I very rarely use Date::Parse or Date::Manip. The core module Time::Piece usually does everything I need. Here is an SSCCE showing it correctly parsing the two sample dates which you gave in your prose (but which do not appear in your code sample).

    use strict;
use warnings;

use Time::Piece;
use Test::More tests => 6;

my $str = '31/08/2024';

my $tp = Time::Piece->strptime ($str, '%d/%m/%Y/');
is $tp->mday, 31;
is $tp->mon, 8;
is $tp->year, 2024;

$str = '01/09/2024';

$tp = Time::Piece->strptime ($str, '%d/%m/%Y/');
is $tp->mday, 1;
is $tp->mon, 9;
is $tp->year, 2024;

    [download]

    If you want to persist with Date::Parse and/or Date::Manip that's fine but you will doubtless help others to help you by showing the problem as an SSCCE like this.

    🦛

[reply]
[d/l]
Re: date parsing
by ikegami (Patriarch) on Jun 13, 2025 at 10:35 UTC

    The data of 01/09/2024 (ie the next calendar day) gives gibberish - with the day coming out as 09 instead of 1, month as 01 instead of 9 and the year as 124.

    It's not gibberish; it just guessed incorrectly about the order of the day and month. Instead of using a parser that guesses at the format, use one that where you can specify the format.

[reply]
      In order to interpret 01/09 as DD/MM instead of MM/DD using Date::Manip (not Date::Parse), just add the line (near the start): 
         Date_Init("DateFormat=non-US");

      [download]
[reply]
[d/l]
Re: date parsing
by ysth (Canon) on Jun 13, 2025 at 04:53 UTC
    Do you mean an OpenDocument Spreadsheet (ods) file? LO Calc works with a lot of file formats, that's just its default. Can you show how you are reading the file?
[reply]
Re: date parsing
by NERDVANA (Priest) on Jun 13, 2025 at 15:25 UTC
    Dates in Excel are stored as numbers, with a format specifier to make them look like dates. Dates coming from users in different locales might default to different date formats, and lead to breakage in your script later. I recommend using $cell->type to check for dates and then ParseExcel's date utility functions to process the underlying number instead of the formatted representation.
[reply]

Back to Seekers of Perl Wisdom