Convert Excel Date into Timestamp

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.

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.

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.

Contact Us
  • Nanyang Technological University, Singapore
  • root [at] haozhexie [dot] com