Thursday 9 June 2011

Convert Excel date into timestamp

Excel stores dates internally as 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 timestamp would be:
=(B2-DATE(1970,1,1))*86400
Now you only need to read the calculated value from the hidden cell.

6 comments:

pannu said...

Awespme.This worked well
=(B2-DATE(1970,1,1))*86400
Thanq Filip

pannu said...

Awesome Thaks Filip.
This worked very well

gaulucky92 said...

Very simple, works perfectly. Thanks a lot!

Matthew Rankin said...

There were only 17 leap years between 1900 and 1970, so I believe you should use 25567 instead of 25569. Agreed, or am I missing something?

List of leap years:

- http://kalender-365.de/leap-years.php
- http://www.miniwebtool.com/leap-years-list/?start_year=1900&end_year=1970

michaely michael said...

No, it's definitely 25569 this is a standard number cited in various sources for doing this. Note however that this may only give you a 10 digit UNIX timestamp when in fact UNIX timestamps are supposed to be 13 digits in length

Unknown said...

Hi,just adding to what Michael said, 25569 should be used as base when converting unix time. Also it is important to note that Unix time do not support leap seconds so it always add exactly 86400 seconds per day