Re: Creating Excel file using Perl

[tangent]

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

[Rahul Saxena]

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.

[sundialsvc4]

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).