Read Date Format in PHP EXCEL

To retrieve the date you have to convert from Excel Timestamp to Linux Timestamp. What you need to know is:

• Excel express dates in days while Linux do it in seconds.
• Excel start to count from year 1900 while Linux do it from year 1970.
• All timestamps are in GM (greenwitch meantime), so you should not convert it with gmdate(); but instead use just date();

So, in order to convert from Excel to Linux you have to:

• remove 70 years of days: 25569
• multiply for seconds in a day: 86400

This is the code:

function fromExcelToLinux($excel_time) {
    return ($excel_time-25569)*86400;
}

$linux_time = fromExcelToLinux(30637); 
echo date("Y-m-d",$linux_time);
//should print 1983-11-17

That's all the folks.

When you read xls file with PHPExcel_Reader_Excel5 lib, the data in file is 39984, but Excel formats it using a date formatting mask as '2009-06-20'?

Excel holds dates as a count of the number of days since 1st January 1900 (on Windows 1900 calendar). PHPExcel stores its dates in the same way, but doesn't automatically format them for you.

You can format them yourself using PHPExcel_Style_NumberFormat::toFormattedString(39984,PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY)
or any of the other format masks in
PHPExcel_Style_NumberFormat
, or convert it to a PHP date using
PHPExcel_Shared_Date::ExcelToPHP(39984) and then use PHP's date()
function to format it as you wish

Example:

$val = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));

An other way is using gmdate:

$excel_date = 43010; //here is that value 41621 or 41631
$unix_date = ($excel_date - 25569) * 86400;
$excel_date = 25569 + ($unix_date / 86400);
$unix_date = ($excel_date - 25569) * 86400;
echo gmdate("Y-m-d", $unix_date);

//result is 2017-10-02