custom date format when viewing file

Topics: User Forum
Sep 10, 2012 at 9:50 PM

I am writing to an excel file that has a custom date format in one of the columns.  The format is 'd-mmm' so it should look like '10-Sep'.  But when I open it up after writing to it, that specific cell that I just wrote to looks like '9/10/12' until i double click on the cell to place the cursor in it and deselect the cell.  Only then will it show up as '10-Sep'.  Anyone know how to make this show correctly when I first open it?

Thanks

Mike

Jun 28, 2013 at 6:25 PM
Hi,

I get the same problem.
Don't know what to try now...

$sheet->setCellValue("E5",$curDate->format("d/m/Y")); / also tried to put the date with Y-m-d format, same result /
$sheet->getStyle("E5")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);

When the file is opened with Excel : E5 contains 12/05/2013 and if I click on cell then exit, the date is shown in the wanted format, so I was well saved for this cell...

Also, when I change the format of this cell with Excel (number or date), nothing happend until I click and exit from the cell. It's like if the date was considered as a string value, and only as a date after this action.

Hoping for any help,
Kind regards,
Michel
Jun 28, 2013 at 6:37 PM
Ok,
I understood...
I gave a string so I got a string at the arrival :-)

$curDate=DateTime::createFromFormat("d/m/Y","12/05/2013");
$dtExcel=PHPExcel_Shared_Date::PHPToExcel($curDate);
$sheet->setCellValue("E5",$dtExcel);
$sheet->getStyle("E5")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);

That works, even with my French locale.

Regards,
Michel