CODEWITHSUNDEEP
phptimephpexcel
Sparfy
Sparfy

Reputation: 267

PHPExcel String to Time

I have created an Excel spreadsheet. In the second column I have values like 0:11:23 and 2:03:33, several thousand rows worth. Using PHP I set the format to be:

    $sheet->getStyle($colRange)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);

But the cells are all still just strings. If you edit a cell from 0:11:22 to something else like 0:11:33 then the cell WILL get converted into a time. At the top it goes from 0:11:22 to 0:11:33 AM this auto addition of AM is what seems to be the huge change, and then I can do what I would like with the file.

How can I change the thousands of cells from 0:11:22 to 0:11:22 AM ?

Upvotes: 3

Views: 6464

Answers (3)

xShen
xShen

Reputation: 572

This is an old post but maybe it would help someone...

$objPHPExcel->getActiveSheet()
->setCellValue('E5', '=TEXT(TIMEVALUE("'. $the_str_Time . '"),"[h]:mm")');

The TIMEVALUE function turns the string into time, And the TEXT function gives them a format as desired. Mine was for measuring time for more then 24 hours

Upvotes: 2

Mark Baker
Mark Baker

Reputation: 212412

That's because you have to convert the date/time to an MS Excel serialized timestamp, and store that value in the cell; as described in the PHPExcel Documentation and shown in 02types.php in the /Examples folder.

Once you've stored an actual MS Excel timestamp value, then you can apply a format to it

$objPHPExcel->getActiveSheet()->setCellValue('A12', 'Date/Time')
    ->setCellValue('B12', 'Time')
        ->setCellValue('C12', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
$objPHPExcel->getActiveSheet()
    ->getStyle('C12')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);

Upvotes: 0

Denny Sutedja
Denny Sutedja

Reputation: 538

try this

$sheet
    ->getStyle($colRange)
    ->getNumberFormat()
    ->setFormatCode('[$-C09]d mmm yyyy;@');

Upvotes: 0

Related Questions