Writing a formatted date to a cell

Topics: User Forum
Sep 9, 2010 at 10:16 AM

I'm trying to store a formatted date in an Excel 2007 worksheet using PHPExcel 1.7.4 and PHP 5.3.2. I'm using a Unix timestamp (stored in $timestamp) which corresponds to 2010-01-29 00:00.

I have tried putting the Unix timestamp in manually and formatting the cell, like so:

$objPHPExcel->getActiveSheet()->SetCellValueByColumnAndRow(3, $currentrow, $timestamp);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(3, $currentrow)->getNumberFormat()->setFormatCode('dd/mm/yyyy');
However, if I open the spreadsheet in OpenOffice the value is 04/07/-8814 and in Excel 2007 ##### is shown (the timestamp appears in the formula bar), even though the cell type shows as Date.

I have also tried converting the timestamp to a string and not bothering with formatting, using the following code:

$date = date('d/m/Y', $timestamp);
$objPHPExcel->getActiveSheet()->SetCellValueByColumnAndRow(3, $currentrow, $date);
This works in Excel (cell type is General so I'm not sure if sorting will work), but in OpenOffice the cell value is '29/01/2010 - i.e. there is a quote mark before the date.

Am I doing something wrong, or is this a known bug with dates? Ideally I would like to add the timestamp and format the cell as a Date, because that way sorting should work in the spreadsheet.

Coordinator
Sep 9, 2010 at 10:52 AM

PHPExcel will only convert a formatted date string to a date value automatically (like Excel) if you are using the advanced value binder (see example 29advancedvaluebinder.php in the /Tests directory of the distribution set for an example of using this).

Otherwise, you need to store an Excel date/timestamp in the cell (rather than a Unix timestamp) and then set the cell format mask to a date format. A number of method calls are provided to allow you to convert between Excel date/timestamp values and PHP/Unix date/times:

PHPExcel_Shared_Date::ExcelToPHP($excelDate)

Converts a date/time from an Excel date timestamp to return a PHP date/time timestamp.

Note that this method does not trap for Excel dates that fall outside of the valid range for a PHP date timestamp.

PHPExcel_Shared_Date::ExcelToPHPObject($excelDate)

Converts a date from an Excel date/time timestamp to return a PHP date/time object.

PHPExcel_Shared_Date::PHPToExcel($PHPDate)

Converts a PHP date timestamp or a PHP date/times object to return an Excel date timestamp.

PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0)

 

$objPHPExcel->getActiveSheet()->SetCellValueByColumnAndRow(3, $currentrow, PHPExcel_Shared_Date::PHPToExcel($timestamp));
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(3, $currentrow)->getNumberFormat()->setFormatCode('dd/mm/yyyy');
Sep 9, 2010 at 12:30 PM

Thanks for the quick reply, using PHPToExcel seems to have fixed the problem - previous posts I'd found suggested that the conversion would happen automatically, but I presume they were using the advanced binder (wasn't entirely clear and I've only just started using PHPExcel).

Nov 1, 2010 at 3:04 PM

How I can set datetime value in a cell so, that it will be in Moscow timezone?

Coordinator
Nov 1, 2010 at 10:04 PM
kabanovdmitry wrote:

How I can set datetime value in a cell so, that it will be in Moscow timezone?

 Excel does not differentiate timezones in any way, and internally PHPExcel uses Excel date/time values. This means that there is no way for you to set a cell date/time value for a specific timezone... it's whatever timezone you want it to be. Without knowing exactly what you want to achieve, it's difficult to advise you any further.

Nov 2, 2010 at 7:28 AM

I have datetime value saved as timestamp.

I put this value in Excel worksheet with such code:

$sheet->getCellByColumnAndRow(1, $row)->setValue(PHPExcel_Shared_Date::PHPToExcel($data['date']));
$sheet->getStyleByColumnAndRow(1, $row)->getNumberFormat()->setFormatCode('dd/mm/yyyy h:mm');

 

Then I open the document in Excel and datetime values are in Greenwich timezone. But I want it to be in Moscow timezone.

 

So I need manually add 3 hours to timestamp and after that feed that value to PHPExcel_Shared_Date::PHPToExcel() method.

 

I just wanted to know if any way to tell PHPExcel to do this task instead of me)

Nov 2, 2010 at 8:17 AM

I've found another solution.

 

I set datetime value as string. I formatted timestamp with PHP-function date and fed it to PHPExcel.

 

Only drawback that in Excel the cell have format General, not Date.