Offset when converting excel time to unix time (two whole days)

General Usage
1

I have data containing excel time stamps (days since 1900-01-01T00:00:00) and trying to convert it to DateTime type. Typically one can google that proper conversion is as follows:

Unix Timestamp = (Excel Timestamp - 25569) * 86400

Fine, let’s double check that “25569” offset number:

julia> using Dates

julia> excel_epoch = datetime2unix(DateTime("1900-01-01T00:00:00", "Y-m-dTH:M:S"));
julia> epoch_unix = datetime2unix(DateTime("1970-01-01T00:00:00", "Y-m-dTH:M:S"));

julia> (epoch_unix - epoch_excel)/86400
25567.0

What is going on here? If I want to rely purely on Dates package I would get the offset 2 days when converting from excel timestamp to unix timestamp.

2

Maybe I’m missing something, but between January 1st, 1900 and January 1st, 1970 there are 365 * 70 + 17 = 25567 days, being 17 the number of leap years (1900 wasn’t a leap year).Where did you find that 25569? Did you check that the conversion with that number is correct?

3

Wikipedia says that the excel epoch is 1900-01-00, which leaves you with one day offset. But the internet indeed suggests 25569, e.g. here.

1 Like
4

Since 1900 is incorrectly treated as a leap year in these systems, January 0, 1900 actually corresponds to the historical date of December 30, 1899.

Maybe that’s where the second day comes from? Reasons are, as so often, legacy.

5

The next line says “January 0, 1900 actually corresponds to the historical date of December 30, 1899”, so yes, the difference is the sum of these things.

6

Thanks guys!