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

Hello, I'm creating a excel file using Perl. But Date values are being not displayed in date format rather it is showing custom format. 'my $dateformat = $workbook->add_format(num_format => ~'mm/dd/yyyy~')' and then write_date_time() function with this format. Can anyone please assist how can I display date values in date format in Excel.

Replies are listed 'Best First'.
Re: Creating Excel file using Perl
by tangent (Parson) on Oct 20, 2015 at 15:07 UTC
    In addition to what shadowsong points out, the date string that you pass to write_date_time() must be in ISO8601 format ( 'yyyy-mm-ddT' or 'yyyy-mm-ddThh:mm:ss.sss' )
    my $format = $workbook->add_format(num_format => 'mm/dd/yy'); $worksheet->write_date_time(0, 1, '2004-05-13', $format); # wrong $worksheet->write_date_time(1, 1, '2004-05-13T', $format); # right
    Results in:
    Row, Col = (0, 1) Value = 2004-05-13 Row, Col = (1, 1) Value = 05/13/04
      hello tangent, My only issue is that it is showing "custom" format, why not 'date' format when displayed in excel. I need to use formula on date values after creating excel file as I am not able to apply formula on custom format. Please help.

        If you are “not able to apply a formula to a custom format,” then this immediately suggests to me that Excel no longer considers the contents of that cell to be a number:   it is now considered to be a string.   And that, therefore, would be the actual root cause of your problem.   You must be certain that you are, in fact, posting a date value, and then (if necessary) instructing Excel to format the cell (range) as you desire.

        Here’s an easy way to check.   In Excel, load the spreadsheet and then change the (date) formatting, say of the column.   Values that truly are “dates” will be reformatted.   Values that are strings, of course, will not.   String-valued columns will allow any string value (including those representing non-existent dates like February 30th).

Re: Creating Excel file using Perl
by shadowsong (Pilgrim) on Oct 20, 2015 at 14:57 UTC

    Hello Rahul

    Try changing

    my $dateformat = $workbook->add_format(num_format => ~'mm/dd/yyyy~')

    To

    my $dateformat = $workbook->add_format(num_format => 'mm/dd/yy')

    It might be easier for us to investigate and provide further assistance if you posted more of your code..

      Hello shadowsong, I have tried what you suggested, but format in excel for date values is still showing "custom". how can I display date values in 'date' format in excel. Please help me.