Convert Excel Date into Timestamp in PHP

Haozhe Xie
2 min read
PHP

Excel stores a date internally as a number of days since January 1, 1900.
For example: “June 9th, 2011 10:30 AM” would be stored as “40703.4375”.
40703 is the number of full days since 01/01/1900 and 0.4375 represents the time (10.5/24 = 0.4375).

When you process dates read from an Excel spreadsheet (e.g., using PHPExcel) you often want to convert them into a UNIX timestamp, i.e. a number of seconds elapsed since midnight of January 1, 1970 UTC.

Note: PHPExcel was abandoned in 2017 in favor of PhpSpreadsheet, which ships a built-in \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp() helper that does this conversion for you. The manual approach below is still worth understanding for how Excel encodes dates, or for when you only have the raw serial number to work with.

Converting in Code

Here is a PHP code to do that:

// Numbers of days between January 1, 1900 and 1970 (including 19 leap years)
define("MIN_DATES_DIFF", 25569);

// Numbers of second in a day:
define("SEC_IN_DAY", 86400);

function excel2timestamp($excelDate) {
    if ($excelDate <= MIN_DATES_DIFF)
        return 0;

    return  ($excelDate - MIN_DATES_DIFF) * SEC_IN_DAY;
}

Although the code above is written in PHP the function should be very similar in any other language e.g. C# or Java. If the provided date is earlier than 1/1/1970 then the minimal timestamp value will be returned.

Two details about the MIN_DATES_DIFF offset are worth knowing. The value 25569 is the number of days Excel counts between its own epoch (January 1, 1900) and the UNIX epoch (January 1, 1970), and it deliberately includes February 29, 1900, a date that never existed but that Excel’s date system treats as valid for historical compatibility with Lotus 1-2-3. Also note that an Excel serial date carries no timezone, so the function effectively interprets it as UTC; if your spreadsheet stores local times, add your UTC offset to the result.

Alternative Solution

If you provide the Excel spreadsheet that you, later on, read from in your app you could add a hidden cell that would calculate the timestamp for you, within the spreadsheet.
Assuming that B2 is the cell that stores your date the formula for calculating the timestamp would be:

=(B2-DATE(1970,1,1))*86400

Now you only need to read the calculated value from the hidden cell.

References