How to retrieve date from table cell using PhpSpreadsheet?

Question

I have xlsx tables and I use PhpSpreadsheet to parse them. Some cells are formatted as date. The problem is that PhpSpreadsheet returns the values from date-formatted cells in an unspecified format:

// What it looks in excel: 2017.04.08 0:00
$value = $worksheet->getCell('A1')->getValue();  // 42833 - doesn't look like a UNIX time

How to get the date from a cell in form of a UNIX time or a DateTimeInterface instance?

score 63 · Accepted Answer · answered Jun 01 '17 at 10:15

63

The value is amount of days passed since 1900. You can use the PhpSpreadsheet built-in functions to convert it to a unix timestamp:

$value = $worksheet->getCell('A1')->getValue();
$date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($value);

Or to a PHP DateTime object:

$value = $worksheet->getCell('A1')->getValue();
$date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);

answered Jun 01 '17 at 10:15

Finesse

9,793
7
62
92

4

If I'm iterating the cells via `$row->getCellIterator()`, do you happen to know how to determine whether this cell's value is supposed to be a date (and then correctly calls `excelToTimestamp`)? – Andreas Wong Jul 02 '18 at 13:25
1

@AndreasWong, it is a subject for a separate question. If you have already asked the question, give me a link. – Finesse Jul 03 '18 at 07:11
For people using `$excelSheet->toArray()`, I underline that you should pass a parameter to specify not to format dates: `$excelSheet->toArray(NULL, TRUE, FALSE)`. Later you can get a `DateTime` according to this answer. – luca.vercelli Feb 15 '23 at 12:41

score 17 · Answer 2 · answered Dec 28 '18 at 12:00

17

When we are iterating with $row->getCellIterator() or we might have other kinds of value, it might be useful to use getFormattedValue instead

while using getValue()

Full name: Jane Doe => "Jane Doe"
DOB: 11/18/2000 => 36848.0

while using getFormattedValue()

Full name: Jane Doe => "Jane Doe"
DOB: 11/18/2000 => "11/18/2000"

answered Dec 28 '18 at 12:00

Jay

641
9
11

9

make sure you set it to false $reader->setReadDataOnly(FALSE); otherwise the getformattedvalue() function won't do any change – jean davy Nizigama Aug 06 '20 at 11:28
thank you @jeandavyNizigama Without your comment, answer is of no use. – iniravpatel May 13 '21 at 08:00

score 11 · Answer 3 · answered Sep 20 '19 at 06:14

Since i cant add comment just adding an answer for future users. you can convert a excel timestamp to unix timestamp by using the following code (from accepted answer)

 $value = $worksheet->getCell('A1')->getValue();
 $date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($value);

And you can determine whether the given cell is Date Time using \PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell) function.

score 3 · Answer 4 · answered Oct 28 '20 at 21:27

I hope my answer will complete for those who are lost with reading date. I also had an issue with a column containing dates. When I read in Excel all dates were at the format d/m/Y, but using PhpOffice\PhpSpreadsheet, some lines were read as d/m/Y and others as m/d/Y. Here is how I do:

First, I check the format with ->getDataType()

my default format is m/d/Y but when ->getDataType() returns 's' it becomes d/m/Y

$cellDataType = $objSheetData->getCell("D".$i)->getDataType();
$cellFormat = 'm/d/Y';
if ($cellDataType=='s'){
    $cellFormat = 'd/m/Y';
}
$resultDate=\DateTime::createFromFormat($cellFormat, $sheetData[$i]['D']);

it works fine

score 0 · Answer 5 · answered Jul 20 '22 at 13:20

None of the above worked for me on a Symfony 5 application.

But this did:

$birthdate =
  \DateTime::createFromFormat('Y-m-d', ($sheet->getCellByColumnAndRow($col,$row)->getFormattedValue()));

If you use a different format on your Excel make sure to change it.

How to retrieve date from table cell using PhpSpreadsheet?

5 Answers5

Linked

Related