Geek Answers Handbook

Convert PHP date to Excel?

The Excel date format is, in my opinion, the number of days since December 30, 1899. (Why? Because it is based on 1/1900, but they mistakenly include a leap day in 1900, and it is based on one. This is normal for the first two months in 1900, but then everything goes wrong. 1 / 1/1901 in Excel is "367".)

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

In PHP 5.1.6?

Yes, bet that you have not seen anyone come. Therefore, I do not have DateTime, date_create (), date_diff () objects available, etc.

Is it possible without a simple record of the day of Excel for 1/1/1970 and work from there? And is there a version protected by DST?

+4
source share
1 answer

If I am not mistaken, there are 86,400 seconds per day and 25,569 days between December 30, 1899 and January 1, 1970. Thus, to convert a Unix timestamp to an Excel date, the formula

ExcelDate = 25569 + UnixTS / 86400

must work. Of course, this formula is only valid for UTC. (He also ignores leap seconds.) For other time zones, if you know the offset (in seconds!) From UTC, you can simply add it to the timestamp before using the above formula.

+9
source

More articles:


All Articles