CODEWITHSUNDEEP
phpphpexcel
neubert
neubert

Reputation: 16782

PHPExcel returns a weird date format

When I use PHPExcel's getFormattedValue() on a row / column that, in LibreOffice Calc, shows up as 02/19/2015 23:59:40 I get 42067.458524537 back. How do I convert that to 02/19/2015 23:59:40?

My PHP code is as follows:

<?php
include('/path/to/PHPExcel.php');

$filePath = 'filename.xlsx';

$inputFileType = PHPExcel_IOFactory::identify($filePath);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$excel = $objReader->load($filePath);
$worksheet = $excel->getSheet();

echo $worksheet->getCellByColumnAndRow(3, 4)->getFormattedValue();

Upvotes: 0

Views: 1660

Answers (1)

Mark Baker
Mark Baker

Reputation: 212402

MS Excel stores dates as a serialized value, a real number that is a count of the number of days since a baseline of either 1st January 1900 or 1st December 1904, depending on whether the spreadsheet was created using the Windows 1900 calendar or the Mac 1904 calendar.

A serialized value of 42067.458524537 corresponds to a date of 4th March 2015 at 11:00:17 (with the Windows 1900 Calendar)

If you've used PHPExcel's getFormattedValue() method, then it should convert the serialized value to a formatted date/time string based on the number format mask applied to that cell.... assuming that you didn't set loadDataOnly when loading the spreadsheet file.

If you need to convert the raw MS Excel serialized value to a unix timestamp or to a PHP DateTime object, then you can use the PHPExcel_Shared_Date::ExcelToPHP() or PHPExcel_Shared_Date::ExcelToPHPObject() methods respectively; then use the native PHP date() of DateTime::format() functionality to format it however you want.

Upvotes: 1

Related Questions