How to format a cell for time data

Topics: Developer Forum
Feb 20, 2012 at 12:38 PM

Hello,

I've some data that i wanted to write in an xls file, but can't find anywhere how to format a cell to TIME :

what i want :

$time = '03:30:00' i'd like to have in the xls file : 3:30

$time = '07:00:00' -> 7

finally, i will add the value with SUM formula wich is working with the test n°2 if time is a rounded value, otherwise no (because of TYPE_NUMERIC of course)

here are my differents tests, but not working :

1 : $sheet->setCellValueExplicitByColumnAndRow($row, $num, PHPExcel_Style_NumberFormat::toFormattedString($time));


2: $sheet->setCellValueExplicitByColumnAndRow($row, $num, '03:30:00', PHPExcel_Cell_DataType::TYPE_NUMERIC); // if only i could do a FORMAT_DATE_TIME6, but not working


//$sheet->getStyleByColumnAndRow($row, $num)->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME6);
//$sheet->setCellValueExplicitByColumnAndRow($row, $num, $time, PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME6);

Thx for helping me or redirect me to an answer

Julien                 

Feb 21, 2012 at 4:56 PM

Up !

Is my question stupid ?

tell me if u need more informations.

 

thank you !

Coordinator
Feb 21, 2012 at 9:53 PM

Section 4.6.3. entitled "Write a date into a cell" covers exactly this topic, with sample code showing three different methods of writing a date to a cell.

In Excel, dates and Times are stored as numeric values counting the number of days elapsed since 1900-01-01. For example, the date '2008-12-31' is represented as 39813. You can verify this in Microsoft Office Excel by entering that date in a cell and afterwards changing the number format to 'General' so the true numeric value is revealed.

/* PHPExcel_Cell_AdvanceValueBinder required for this sample */
require_once 'PHPExcel/Cell/AdvancedValueBinder.php';

// MySQL-like timestamp '2008-12-31' or date string
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
$objPHPExcel->getActiveSheet()
           ->setCellValue('D1', '2008-12-31');
$objPHPExcel->getActiveSheet()
           ->getStyle('D1')
           ->getNumberFormat()
           ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

 or

// PHP-time (Unix time)
$time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
$objPHPExcel->getActiveSheet()
           ->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel($time));
$objPHPExcel->getActiveSheet()
           ->getStyle('D1')
           ->getNumberFormat()
           ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

or 

// Excel-date/time
$objPHPExcel->getActiveSheet()
           ->setCellValue('D1', 39813)
$objPHPExcel->getActiveSheet()
           ->getStyle('D1')
           ->getNumberFormat()
           ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

The PHPExcel_Shared_Date::PHPToExcel() method will also work with a PHP DateTime object.

 

And because Excel holds dates/times as numeric values, it is possible to SUM them: this works well with time values, though not so well with dates. Just set the appropriate number format mask for the cell with the formula.

 

While I appreciate that many people dislike reading documentation, or consider that the PHPExcel documentation is "rubbish"; it does answer a lot of these basic questions, and I'm not on call for immediate support 24 hours a day every day.

Feb 22, 2012 at 12:21 PM

First of all, a big thanks for your explanation, and sorry for the up but i really tought that i missed something and made a stupid question.

I go try these solutions and i'll paste my choice.

Thank a lot for the support !

Feb 22, 2012 at 2:01 PM

Well, i tried to adapte your propositions to my case but no success :x

Ok with the excel's date system but i dont want a date from but only a time like 3:30 or 07:00

Your second example may be the best for me, so :


$duree = '08:00:00';
list($hour, $min, $sec) = explode(':', $duree);
$time = gmmktime($hour,$min);


$sheet->setCellValueByColumnAndRow($row, $num, PHPExcel_Shared_Date::PHPToExcel($time));
$sheet->getStyleByColumnAndRow($row, $num)
          ->getNumberFormat()
          ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);

and in my cell i can see 08:00 wich is ok but in the formula cell the data is 983072:00:03 and so the "SUM" don't work.

what am i doing wrong ?

Coordinator
Feb 22, 2012 at 4:41 PM
Edited Feb 22, 2012 at 4:43 PM

That's because gmmktime() defaults the missing arguments to today. Either set the year,month,day arguments to PHP's base date (1970,1,1) or just feed the time string to PHPExcel_Shared_Date::PHPToExcel();

PHPExcel_Shared_Date::PHPToExcel($duree);

This is where PHPExcel tries to be cleverer than Excel itself.

 

or

$time = gmmktime($hour,$min) - gmmktime(0,0,0);
Feb 22, 2012 at 7:26 PM

I tried your 2 solutions but that don't work better :

1st try :

$sheet->setCellValueByColumnAndRow($row, $num, PHPExcel_Shared_Date::PHPToExcel('07:00:00'));

$sheet->getStyleByColumnAndRow($row, $num)
          ->getNumberFormat()
          ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);

In my Excel file i only get "=FAUX()" (=FALSE() ? i use a french openoffice, perhaps the trouble come from here)

2nd try :

list($hour, $min, $sec) = explode(':', '07:00:00');
$time = gmmktime($hour,$min) - gmmktime(0,0,0);

$sheet->setCellValueByColumnAndRow($row, $num, PHPExcel_Shared_Date::PHPToExcel($time));
$sheet->getStyleByColumnAndRow($row, $num)
          ->getNumberFormat()
          ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);

This give me the same result as in my previous post

 

thx again for your appreciate help !

Coordinator
Feb 22, 2012 at 11:18 PM
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
$sheet->setCellValueByColumnAndRow($row, $num, '07:00:00');
$sheet->getStyleByColumnAndRow($row, $num)
      ->getNumberFormat()
      ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);

and you're right: $time = gmmktime($hour,$min) - gmmktime(0,0,0); will give time on 1st Jan 1970... you only want the fractional part so:

list($hour, $min, $sec) = explode(':', '07:00:00'); 
$time = gmmktime($hour,$min); 
$sheet->setCellValueByColumnAndRow($row, $num, fmod(PHPExcel_Shared_Date::PHPToExcel($time),1));
$sheet->getStyleByColumnAndRow($row, $num)
      ->getNumberFormat()
      ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);

 

Feb 23, 2012 at 12:19 AM
Edited Feb 23, 2012 at 12:30 AM

So cool !

your first solution work very well !

I tried the second but the result is still wrong : this is the cell value for '07:00:00' is 07:00:17 certainly a small conversion problem.

Thank you very much and he ll come more often to view if i can help others !

Maybe a suggestion for future it would be great to have a FORMAT_DATE_TIME9 ;) for 07:00:00 -> 7  and 03:30:00 -> 3:30 dont think it is actually possible.

Coordinator
Feb 23, 2012 at 6:42 AM

You can actually set whatever format masks you want, you're not limited to the predefined set. This mimics MS Excel itself, which has a number of predefined date masks, but you can also apply "custom" masks as well.

$sheet->getStyleByColumnAndRow($row, $num)
      ->getNumberFormat()
      ->setFormatCode("hh");

will give you just the hours

 

 

Feb 23, 2012 at 5:36 PM

Nice to know, after try it appears that Excel doesn' know this format -> like the php date('G'). 07:00:00 can't be only 7, at best 07:00

Coordinator
Feb 23, 2012 at 8:00 PM

My version of MS Excel allows both "h" and "hh" as format masks, the former will show only 1 digit if the hours to display is <= 9, the latter will show a leading 0.

Feb 23, 2012 at 8:47 PM

yes this work for the hour : setFormatCode("H:MM");

but can t round minutes because i ve got 2 possibles case : time can be like 07:00:00 or like 01:30:00 so i ve to use MM.