Phpspreadsheet - Time cell retrieved as float

Question

I have an excel file which has a time input.

  Mar 01, 2018  | Thursday  | 8:00 AM | 5:00 PM
  Mar 02, 2018  | Friday    | 8:00 AM | 5:00 PM

But when my code tries to read those cells, the output becomes a float (for example 8:00 AM becomes 0.33333333333333). This is my code

$date = $sheet->getCell("A".$row)->getValue();
$time_in = $sheet->getCell("C".$row)->getValue();
$time_out = $sheet->getCell("D".$row)->getValue();

echo "date: ".$date.          //Mar 01, 2018
     " time_in: ".$time_in.   //becomes 0.333333333333333
     " time_out: ".$time_out; //becomes 0.708333333333333

How can I make the output as is without the phpspreadsheet changing the value? I have tried looking at the phpspreadsheet documentation but I haven't found a solution.

Red Magda · Accepted Answer · 2019-08-07T08:45:30.253

12

Thankfully I have found the answer just now.

$in = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($time_in);
echo gmdate("g:i a", $in);

Hopefully, this could be useful for others.

edited Aug 07 '19 at 08:45

answered Mar 07 '18 at 01:20

Red Magda

398
4
17

I suggest using gmdate("g:i a", $in) instead of date so that it is not a day off. For instance, if your system is set to EST date will convert the date/time using -5. ex April 19, 2019 1 AM will become April 18, 2019 8 PM – Johnish Jun 20 '19 at 19:58

score 1 · Answer 2 · answered Oct 12 '20 at 10:31

Formart the data before you read it.

For example:

cell data 2020/10/13 19:00:00

Access directly will get 44117.791666667

$sheet->toArray();

Format before access will get 2020-10-13 19:00:00

$row_num = $sheet->getHighestDataRow();
$sheet->getStyle("H1:H$row_num")->applyFromArray(array("numberFormat"=>array("formatCode"=>'yyyy-mm-dd hh:mm:ss'))); // H is the datatime row
$sheet->toArray();

Phpspreadsheet - Time cell retrieved as float

2 Answers2