Problem with Time formatting - solved

Topics: User Forum
Dec 23, 2009 at 9:00 AM

When I write a timestamp ('10:54:45') to a cell, the timestamp is actually stored in excel as '2009/12/22 10:54:45' . Now I have a whole column of these timestamps and our client requires us to do a sum of them.

This is obviously not working, I must be doing something really stupid here, but I can't seem to figure out what.

I use PHPExcel version 1.7.1

The code I use to write the data to the cells is the following

$this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column,$rowNr,$timeStamp);
$this->objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($column, $rowNr)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8);

 

I Initialize the PHPExcel classes with the following code

$objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel2007');
$objWriter->setPreCalculateFormulas(false);
$objWriter->setOffice2003Compatibility(true);

 

Thanks in advance guys!

I really love PHPExcel, you guys are doing a great job!

Coordinator
Jan 1, 2010 at 1:45 AM

How are you setting the value $timeStamp

Jan 4, 2010 at 3:02 PM
MarkBaker wrote:

How are you setting the value $timeStamp

$timeStamp = '10:54:45';

 

Something like this.

Coordinator
Jan 5, 2010 at 11:52 AM
NikiVanCleemput wrote:
MarkBaker wrote:

How are you setting the value $timeStamp

$timeStamp = '10:54:45';

 Actually entering a string value like that won't work, you need to set the cell value as an Excel timestamp value.

One option would be to use the Excel TIME() or TIMEVALUE() functions to calculate the Excel timestamp:

$timeStamp = PHPExcel_Calculation_Functions::TIME(10,54,45);

or

$timeStamp = PHPExcel_Calculation_Functions::TIMEVALUE('10:54:45');

 

Jan 6, 2010 at 1:40 PM
MarkBaker wrote:
NikiVanCleemput wrote:
MarkBaker wrote:

How are you setting the value $timeStamp

$timeStamp = '10:54:45';

 Actually entering a string value like that won't work, you need to set the cell value as an Excel timestamp value.

One option would be to use the Excel TIME() or TIMEVALUE() functions to calculate the Excel timestamp:

$timeStamp = PHPExcel_Calculation_Functions::TIME(10,54,45);

or

$timeStamp = PHPExcel_Calculation_Functions::TIMEVALUE('10:54:45');

 

I'm using --> $timeStamp = PHPExcel_Calculation_Functions::TIMEVALUE('10:54:45');

 

Works very great, thank you!

 

Now while I'm still here, 1 more question. If I do a formula like 'SUM(A10:A20)', what formatting can I use to get hours that count more than 24 .

For example '36:12:42'

 

Coordinator
Jan 6, 2010 at 9:38 PM
NikiVanCleemput wrote:

If I do a formula like 'SUM(A10:A20)', what formatting can I use to get hours that count more than 24 .

For example '36:12:42' 

In Excel itself, you'd use [h]:mm:ss as the format mask: try that.

Jan 7, 2010 at 10:21 AM

Thanks that did the trick.

 

Thanks for the help, very much appreciated.

Developer
Jan 14, 2010 at 8:54 AM

I modified AdvancedValueBinder.php so one can also do like in example below.

Example:

 

require_once 'PHPExcel/Cell/AdvancedValueBinder.php';
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

$excel = new PHPExcel();
$worksheet = $excel->getActiveSheet();

$worksheet->getCell('A1')->setValue('10:54:45');

 

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=11492

Try with latest source code tomorrow:

http://phpexcel.codeplex.com/SourceControl/list/changesets