Time more than 24 hours

Topics: User Forum
Feb 22, 2012 at 11:35 PM

Hi, i have a problem with time format. I summarize times from other cells, but Cell for storage accept only 23:59 as maximum. I need use [h]:mm format instead of h:mm, but PHPExcel dont support his. It is possible to add a new format into library? Have PHPExcel some other way for storage more than 24 hours in cell?

Coordinator
Feb 23, 2012 at 6:47 AM

I thought this did work, I'll need to investigate.

Coordinator
Feb 23, 2012 at 8:37 AM

Using the following code:

// Array of hrs, mins, seconds values
$timeSeries = array(array(3,30,15),
     array(11,15,25),
     array(7,20,45),
     array(9,40,10),
     array(3,15,25),
       );
$row = 1;
// Set values in cells
foreach ($timeSeries as $timeData) {
 $time = call_user_func_array('gmmktime',$timeData);
 $objPHPExcel->getActiveSheet()
             ->setCellValue('A'.$row++, fmod(PHPExcel_Shared_Date::PHPToExcel($time),1));
}
// Use a formula to calculate the total hours
$objPHPExcel->getActiveSheet()
            ->setCellValue('A'.$row, '=SUM(A1:A'.($row-1).')');
$objPHPExcel->getActiveSheet()
            ->setCellValue('B'.$row, 'Total Hrs');
// Set style for individual value cells
$objPHPExcel->getActiveSheet()->getStyle('A1:A'.($row-1))
            ->getNumberFormat()
            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);
// Set style for the "total" cell
$objPHPExcel->getActiveSheet()->getStyle('A1:A'.$row)
            ->getNumberFormat()
            ->setFormatCode('[h]:mm');
echo $objPHPExcel->getActiveSheet()->getCell('A'.$row)->getFormattedValue(),PHP_EOL;

The data is written cleanly to Excel2007 or Excel5 workbooks so that the total hours is shown correctly. The only problem is the getFormattedValue() call which is failing to format the result, but simply returning the numeric value (1.4597222222219 in this case).

 

Feb 26, 2012 at 9:21 PM

Thanks, it's working!

Jul 4, 2013 at 11:01 AM
Edited Jul 4, 2013 at 11:05 AM
Hi,

but what about passing variable to cell. I'm trying to pass to cell variable with value 27:00:00 with format [h]:mm.

example:
$phpEx -> getActiveSheet() -> setCellValueByColumnAndRow($start_x+$counter, $start_y+2+$i,'27:00:00');
$phpEx -> getActiveSheet() -> getStyleByColumnAndRow($start_x+$counter, $start_y+2+$i)->getNumberFormat()->setFormatCode('[h]:mm');
The problem is that value is written to the cell but without formating and this value is not taken to summary bellow cells.

Image

All data is ok besides the cell with 27:00:00 (also summary is bad because of that). And i've tried also passing 23:59:00 which was correct and 24:00:00 which is wrong :).

Any idea? :)
Jul 4, 2013 at 11:34 AM
Viewing the example given by Mark: time is not set as a string but as a number.
Coordinator
Jul 4, 2013 at 12:04 PM
Edited Jul 4, 2013 at 12:04 PM
Formatting time for hours greater than or equal to 24, try using
`'[hhh]:mm'`
as per MS Excel number format rules
Jul 4, 2013 at 1:50 PM
Sorry - i missed some of this example.
Jul 4, 2013 at 1:55 PM
Mark - it would not help in my case. What I needed to do is to convert string "27:00:00" through unixtime to Exel time number and then pass it to your example.

Basically in your example if you change in $timeSeries array first value to array(33,30,15) it will show 9:30 in excel - that was my problem. How to manage to show in cell 33:30 :).
Jul 4, 2013 at 2:27 PM
Edited Jul 4, 2013 at 3:54 PM
Try this :
$objPHPExcel->getActiveSheet()->setCellValue('A2', (50/24)+(35/(24*60))+(15/(24*60*60)));
$objPHPExcel->getActiveSheet()->getStyle('A2')->getNumberFormat()->setFormatCode('[h]:mm:ss');
Time is 50:35:15.

(ugly code, but this is just to test.)