date in a cell - not compatible with openoffice

Topics: Developer Forum
Jan 7, 2008 at 6:47 AM

I use some dates in my worksheet.
no problem for Excel, fields are recognized, I use for example :

$objPHPExcel->getActiveSheet()->setCellValue('C3','03/01/2008 14:30');

I can after that make my calculation, differences between dates, ..., it works

except with OpenOffice, the field isn't recognized as a date, and an apostrophe is put aat the beginning of the field :

'03/01/2008 14:30

this apostrophe keep OpenOffice to interpret it. If I delete manually this apostrophe in OpenOffice, it works...

I think the date is correctly formated, I read in NumberFormat.php

const FORMATDATEDATETIME = 'd/m/y H:i';

and i use the code

SELECT DATE_FORMAT(dat,'%d/%m/%Y %H:%i') AS dat

in my MySQL request

Have you got an idea what I can do ?

Thank you
Jan 7, 2008 at 7:08 AM
Can you send me the XLSX file? (maarten AT php excel DOT net)
Oct 2, 2008 at 10:26 AM
Edited Oct 2, 2008 at 10:29 AM
We are having the same issue. (Using Excel 5). Do you require the XLS file?

Related question: how can we define custom date formats? E.g., is it possible to do:
$objPHPExcel->getActiveSheet()->getStyle($cellIndex)->getNumberFormat()->setFormatCode('NN D MMM YYYY');
Oct 2, 2008 at 11:16 AM
Edited Oct 2, 2008 at 8:29 PM
This is not the correct way to put a date in a cell:

$objPHPExcel->getActiveSheet()->setCellValue('C3', '03/01/2008 14:30');

Do like this:

$objPHPExcel->getActiveSheet()->setCellValue('C3', gmmktime(14, 30, 0, 3, 1, 2008));

The difference is that in the first case the value gets stored like a string while in the second case the value gets stored as a number which is the correct way of entering dates. Excel, by default, stores dates as numbers counting days since 1900. You can always check that your value is stored correctly as a number by checking the formula =TYPE(C3) which should give you 1 for numbers. Also, in MS Office Excel, you may try to change the number format in the generated spreadsheet to 'General'. This will reveal the real number stored for the date.

Under the hood, gmmktime(14, 30, 0, 3, 1, 2008) gives you Unix time (seconds elapsed since 1970) and a conversion from Unix time to 1900-based Excel date takes place internally in PHPExcel whenever you at the same specify a date-like number format.

You can apply custom number format like this:

$objPHPExcel->getActiveSheet()->getStyle('C3')->getNumberFormat()->setFormatCode('yyyy mmm dd');

Note that you always need to write format code like in the English version of Excel. Here is a reference for format codes.

Important: Please use latest source of PHPExcel because some bugs involving dates were discovered and fixed after the release of PHPExcel 1.6.3. Download here:

After reading your post, I notice some small problems with the predefined format code constants in the PHPExcel_Style_NumberFormat class, so in the meantime use your own custom number format codes. Will follow up on this later.

Oct 2, 2008 at 11:41 AM

Thanks koyama; your instructions are clear and we will be able to set the dates like you suggest.

A question though: shouldn't there be a PHPExcel_Cell_DataType::TYPE_DATE type available, so dates can simply be set like this?

$objPHPExcel->getActiveSheet()->getStyle('C3')->getNumberFormat()->setFormatCode('NN D MMM YYYY');
$objPHPExcel->getActiveSheet()->getCell('C3')->setValueExplicit('2008-03-01 14:30:00', PHPExcel_Cell_DataType::TYPE_DATE );

It would make things quite a lot simpler than having to use the unix timestamp.
Oct 2, 2008 at 12:31 PM
I follow what you are saying, but there may be workarounds. For example, if you retrieve dates from MySQL and need to insert in spreadsheet using PHPExcel, then Unix time may not be the suitable format.

You can however use the PHP function strtotime():

$cell->setValue(strtotime('2008-03-01 14:30:00 GMT'));

Note, it is important to append 'GMT', otherwise strtotime() will return Unix time based on your local timezone settings.
Oct 3, 2008 at 8:31 AM
Edited Oct 3, 2008 at 8:35 AM
The Excel writers do try to show a modicum of cleverness when the formatting of the cell is defined as a date/time format. The actual value stored by the call to $objPHPExcel->getActiveSheet()->setCellValue() can be an integer (in which case it assumes that it is a PHP date/time value, and converts it to an Excel date/timestamp value for writing), a float (in which case it assumes that it is an Excel date/time value already and writes it "as is"), or even a PHP date object.

At the moment, string values are always written "as is", on the premise that the cell probably contains a formula that returns a date/time value rather than that they actually contain a string depiction of a date/time.

For string values, it would be possible to do an additional substring test to see if the first character of a string cell value was an "=" sign (indicating a formula, which should be written "as is"), or not. In this latter case the code could assume that it was a string depiction of a date/time and attempt to convert it to an Excel date/time value. In the event of a successful conversion, write as an Excel date; otherwise (if conversion fails) assume that it is a genuine string value to be written "as is".

Note that these additional tests and conversions would slightly slow down the writer process.
Note also that date/time calculation functions will try to work with a string value depicting a date/time.

If you want the writer code logic changed to work this way, then raise this as a Work Item, and I'll look at doing it.