convert excel dateserial back to a readable date format

Question

How do i convert this excel dataserial value 41225 back to date format 12-Nov-2012 using phpexcel and code igniter?

I have tried the following but it didn't work.

$G74 = $objPHPExcel->getActiveSheet()->getCell('B6')->getValue();

Answer 1

Dates in Excel are stored as number of days since 1st Jan 1900, except there is an off by one error due to 1900 not being a leap year. You can create therefore a DateTime object with this hack (valid for dates from 1st March 1900 onwards):

$n = 41225;
$dateTime = new DateTime("1899-12-30 + $n days");

You can format the DateTime Object with something like:

echo $dateTime->format("d M Y");

If you want to include the time as well as the date, multiply by 86400 (the number of seconds in a day) to get seconds since 1st Jan 1900 before you convert:

$n = 42898.35416666;
$dateTime = new DateTime("1899-12-30 + ". round($n * 86400) . " seconds");

Answer 2

Using the getFormattedValue() method rather than getValue() might help if the cell has a format mask that formats it as a date. getValue() returns a raw value, which (in this case) is the Excel serialized number.

Otherwise, the ExcelToPHP() or ExcelToPHPObject() methods in the PHPExcel_Shared_Date class should do the job of returning a unix timestamp or a PHP DateTime object that you can then format however you wish

Answer 3

I had to convert a dateserial number in decimal format ie: 42898.35416666 and the DateTime object does not take decimals.

Based on rjmunro's answer, and animuson's answer on how to convert decimals to hours and minutes, here is a solution for decimal Excel dates.

function dateSerialToDateTime($dateserial) {
    $arrDate = explode(".", $dateserial);
    $n = $arrDate[0];
    $decimal = "." . $arrDate[1];  //decimal amount of seconds
    $duration = 86400 * $decimal;  //number of seconds in a day * decimal

    $dateTime = new DateTime("1899-12-30 + $n days");
    $converted = $dateTime->format("Y-m-d") . " " . gmdate("H:i:s", $duration);

    return $converted;
}
$dateserial = 42898.35416666;
die(dateSerialToDateTime($dateserial));

//returns 2017-06-12 08:29:59