Convert PHP date to Excel?

Question

Excel's date format is, I believe, the # of days since December 30, 1899. (Why? Because it's based on 1/1/1900 but they erroneously include a leap day in 1900, and it's one-based. So it's fine for the first couple of months in 1900 but then it goes wrong. 1/1/1901 in Excel is "367".)

So, how can I convert a date or timestamp from PHP (which is typically stored as a Unix timestamp, the # of seconds since 1/1/1970) to Excel?

In PHP 5.1.6?

Yeah, bet you didn't see that one coming. So I don't have any of the DateTime objects, date_create(), date_diff(), etc. functions available to me.

Is it even possible without simply recording the Excel day for 1/1/1970 and working from there? And is there a DST-proof version?

Answer 1

If I'm not mistaken, there are 86400 seconds in a day, and 25569 days between 30 Dec 1899 and 01 Jan 1970. So, to convert a Unix timestamp to an Excel date, the formula

ExcelDate = 25569 + UnixTS / 86400

ought to work. Of course, this formula is only correct for UTC. (Also, it ignores leap seconds.) For other timezones, if you know the offset (in seconds!) from UTC, you can just add it to the timestamp before using the formula above.