Excel dates off by one hour

Topics: User Forum
Aug 30, 2012 at 8:26 AM
Edited Aug 30, 2012 at 8:26 AM

I'm having problems when converting dates stored as a Unix timestamp into Excel dates for inserting into a spreadsheet - from April onwards they are all off by one hour (e.g. 01/06/2012 00:00 becomes 31/05/2012 23:00).

I've hardcoded the timestamp here, but in normal use it comes from a INT UNSIGNED column in a MySQL database:


require_once 'includes/phpexcel/Classes/PHPExcel.php';
require_once 'includes/phpexcel/Classes/PHPExcel/Writer/Excel2007.php';
$spreadsheet = new PHPExcel();
$spreadsheet->setActiveSheetIndex(0);$worksheet = $spreadsheet->getActiveSheet();
$php_date = 1338505200;
print date('d/m/Y', $php_date) . "\n";
$excel_date = PHPExcel_Shared_Date::PHPToExcel($php_date);
print $excel_date . "\n";
$worksheet->SetCellValueByColumnAndRow(0, 1, $excel_date);$worksheet->getStyleByColumnAndRow(0, 1)->getNumberFormat()->setFormatCode('dd/mm/yyyy');
$writer = new PHPExcel_Writer_Excel2007($spreadsheet);$writer->save('date-test.xlsx');


When I execute this code, it prints:




And A1 contains the value: 31/05/2012 23:00:00 (displayed as 31/05/2012).

I suspect the problem is due to the change in timezone (we go to BST/GMT + 1 during the summer), but it seems to only affect Excel dates, not PHP ones. Is there any way to fix this? I'm using the latest stable version of PHPExcel (1.7.7).

Sep 1, 2012 at 11:20 AM

Use UST date values... the gmmktime() or gmdate() functions should help here.

Sep 3, 2012 at 9:07 AM

I'm not sure how they would help - I have Unix timestamps already in my database. If I run them through gmdate() I will still end up with the time being one hour out.

For the moment I'm just calling round() on the result on PHPToExcel - it's a bit of a hack but since Excel works off the number of days I can assume that the nearest whole number will be the correct day, and my timezone will never be more than 1 hour away from UTC.