How to format a cell for time data and after in document use sum of this data?

Topics: Developer Forum
Jan 29, 2015 at 5:08 PM
Hi I created time cell by this topic.
I have format:
$d= DateTime::from('00:30:00');
$objPHPExcel->getActiveSheet()->SetCellValue('N' . ($key + 2), $d->format('G:i:00'));

$objPHPExcel->getActiveSheet()->getStyle('N' . ($key + 2))
                ->getNumberFormat()
                ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
All is correct, but when I add in excel function SUM of this values returns 0:00:00. I must click to every cell click ENTER, then function SUM will start process.
Any Idea what is wrong?, or how can I prepare data for this process?

For any help thx.
Coordinator
Jan 29, 2015 at 5:41 PM
Don't store a formatted date as a string in a cell. You can't simply add string values and expect to get a sensible number as a result.

The topic you've linked to shows several methods for converting a date (whether a formatted string, a PHP DateTime object or a unix timestamp) to a MS Excel serialized date/timestamp. Use the result of a call to PHPExcel_Shared_Date::PHPToExcel($d) to set as the cell value, not the result of a call to $d->format('G:i:00')
Jan 29, 2015 at 7:45 PM
Edited Jan 29, 2015 at 8:14 PM
@MarkBaker Thx, and one more question, it is possible use not DateTime? Because I don't need date only time.
I have only '00:30:00' string and in excel is [now_date '00:30:00'] like 29.1.2015 2:30:00
Coordinator
Jan 29, 2015 at 11:22 PM
If you need purely time, then you need to force the date to Excel's baseline date: 31st December 1900. Standard PHP behaviour when setting a DateTime object with purely a time, it will assume today's date. Similarly with unix timestamps, though you can only do this if you're using 64-bit PHP, otherwise the baseline date falls outside the range of a 32-bit unix timestamp.

Alternatively, use PHPExcel_Shared_Date::FormattedPHPToExcel(1899, 12, 31, 0, 30, 0);
Jan 30, 2015 at 8:36 AM
@MarkBaker Thx, when I use this solution I have also date in in excel cell.
And what about this? Is this best particle?
$time = '00:30:00';
$objPHPExcel->getActiveSheet()->SetCellValue('N' . ($key + 2), '=TIMEVALUE("'.$time.'")');