Time calculation

Jun 2, 2008 at 11:35 AM
Hello Community,

I'm inserting time values (ie. 1:05, 2:00, 0:45) into a column and try to calculate them afterwards by the sum method. I'm setting the number format to '[hh]:mm' to get the correct sum.

The problem is, that Excel 2007 does not recognize the values correctly and so the sum is always "0:00". If I edit each cell by doubleclicking and leaving the cell, Excel seems to correct the type and adds the edited amount of time to the final sum.

Here's the part of the code:
...
$objPHPExcel->getActiveSheet()->setCellValue('F'.$counter, $duration);
$objPHPExcel->getActiveSheet()->getStyle('F'.$counter)->getNumberFormat()->setFormatCode('[hh]:mm');
...
$objPHPExcel->getActiveSheet()->SetCellValue('F'.$counter, '=SUM(F'.($startActivity).':F'.($counter-1).')');
...

Any one gots some hints?

Cheers
Jens Jacobsen
Coordinator
Jun 2, 2008 at 11:47 AM
Can you try this using disabled "pre calculate formulas" (check documentation)?
Jun 2, 2008 at 11:59 AM
Edited Jun 2, 2008 at 12:00 PM
The pre calculate formulas is disabled. I forgot to mention that.

...
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->setPreCalculateFormulas(false);
...

Jun 2, 2008 at 12:16 PM
Here's an example (based on the default tests) for this problem:
http://www.jens-jacobsen.de/22timecalculation.phps
Coordinator
Jun 2, 2008 at 12:46 PM
Setting time with PHP's time() function should work properly (instead of setting a string xx:yy)
Jun 3, 2008 at 8:03 AM
Edited Jun 3, 2008 at 8:05 AM
First of all: Thanks for your time and answering me ;)

But, I cannot get this to work. Here's what I tried

// this would be 1.1.1970 01:00:00 -> 3600
$seconds = (60 * 60);
$objPHPExcel->getActiveSheet()->setCellValue('A1', $seconds);

// how excel changes the entered value '01:00' (in Excel ofc) if you change format from [hh]:mm to default: 0,0416666666667
// so write it to the cell
$objPHPExcel->getActiveSheet()->setCellValue('B1', ($seconds / (24 * 60 * 60)));

// this works in excel if you change the format of B1 to [hh]:mm manually
// now try this in PHPExcel
$objPHPExcel->getActiveSheet()->setCellValue('C1', ($seconds / (24 * 60 * 60)));
$objPHPExcel->getActiveSheet()->getStyle('C1')->getNumberFormat()->setFormatCode('[hh]:mm');
// doesnt work :( prints out: 613657:00 and if you hover the cell in Excel: 01.01.1970  01:00:00
Some more hints? Btw. I also tried to use methods from PHPExcel_Shared_Date, but that didn't work either.
Coordinator
Jun 3, 2008 at 1:24 PM
Edited Jun 3, 2008 at 1:26 PM


jacobsenj wrote:
$objPHPExcel->getActiveSheet()->setCellValue('C1', ($seconds / (24 * 60 * 60)));
$objPHPExcel->getActiveSheet()->getStyle('C1')->getNumberFormat()->setFormatCode('[hh]:mm');
// doesnt work :( prints out: 613657:00 and if you hover the cell in Excel: 01.01.1970  01:00:00
Some more hints? Btw. I also tried to use methods from PHPExcel_Shared_Date, but that didn't work either.


The PHPExcel Excel 7 writer detects that the value is a date because it uses a date mask, and attempts to convert the cell value from a PHP date to an Excel date before writing it to the xlsx file... it isn't aware that this is already a valid Excel date/time serial value.
It's erroring somewhere in that logic, writing an invalid Excel date/time serial value to the worksheet.

I'll try and debug exactly what it's doing tonight.
Coordinator
Jun 4, 2008 at 1:51 PM
Mark, any luck on this one?
Coordinator
Jun 5, 2008 at 7:57 AM
The Excel 2007 writer assumes that any cell value formatted as a date ('[hh]:mm' in this case) is a PHP date/time serial, and so converts it to an Excel date/time serial before writing it to file. The values in this case (the individual durations - such as $seconds / (24 * 60 * 60)));) are already Excel timestamps, so the conversion from PHP to Excel when the value is already Excel causes some of the problem.
If you were to use a PHP time value, you'd still have problems with this conversion because $seconds would be treated as a full date/time value (from the PHP base date of 01-01-1970 01:00:00, so it would convert to an Excel value around that date and time. When displayed with the [hh]:mm mask, this will include the number of days since the Excel base date (01-01-1900, so giving erroneous values). Even though you can use negative values for PHP dates, trying to factor in the difference to give a valid PHP date/time for the duration based on 01-01-1900 so that it will be correctly converted to the right Excel value isn't possible because the absolute base date for PHP is 14-12-1901.


There is a discrepancy between the Excel 5 and Excel 2007 writers: Excel 2007 assumes that date/times are PHP and converts them to Excel data/times before writing, but the Excel 5 writer assumes (correctly in this case) that they are Excel date/time values.


So interim solution is to use the Excel 5 writer; or to comment out the appropriate block of code in the _writeCell() method of the PHPExcel_Writer_Excel2007_Worksheet class.

Longer term solution is to provide a flag that is used to determine whether the writer should convert dates from PHP to Excel before writing, or assume that they are already Excel date/times and simply write them. I'm looking at the date function logic again (having noticed some errors in passing negative values to the DATE function) anyway, modifying it so that you can request the response as an Excel date/time value, PHP date/time value, or a PHP date/time object. While I'm doing that, I'll take a look at providing a flag for determining whether dates passed to the writers should be converted to Excel dates, or written "as is"; and I'll apply the same writing logic to both Excel 5 and Excel 2007.
Coordinator
Jun 5, 2008 at 7:10 PM
Copied into a work item: http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=6302