Does anyone know how to convert an Excel date to a correct Unix timestamp?
Unless it's an answer, please post your own responses/updates as edits to the original question, or as comments. Thanks :)
Matt Ball
2009-11-09 20:36:06
A:
If we assume the date in Excel is in A1 cell formatted as Date and the Unix timestamp should be in a A2 cell formatted as number the formula in A2 should be:
= (A1 * 86400) - 2209075200
where:
86400 is the number of seconds in the day 2209075200 is the number of seconds between 1900-01-01 and 1970-01-01 which are the base dates for Excel and Unix timestamps.
The above is true for Windows. On Mac the base date in Excel is 1904-01-01 and the seconds number should be corrected to: 2082844800
Grendler
2009-11-09 20:46:00
A:
If you mean within Excel, you may be able to do it with a formula like this:
=(A1 - 25569)*86400
Where A1 is a cell set to display as a date. Set the cell with the above formula to display as a plain numder, and I think it will display the timestamp value.
Walter Mundt
2009-11-09 20:46:22
A:
Don't forget that on January 19, 2038 the Unix Time Stamp will cease to work due to a 32-bit overflow. Before this moment millions of applications will need to either adopt a new convention for time stamps or be migrated to 64-bit systems which will buy the time stamp a "bit" more time. Best regards, Carl http://www.couponshouse.com
Coupon
2010-08-23 14:23:05