CODEWITHSUNDEEP
google-sheetsdatetime-formatepoch
user3234550
user3234550

Reputation: 21

Google Sheets Epoch Time

In Google Sheets, while copy-pasting values only i.e CMD+Shift+V, of Date Time objects, they are pasted as floats e.g

Date Time paste values only
04/10/2016 23:58:00 42647.9986111111
15/11/2021 23:58:00 44515.9986111111
25/08/2022 23:58:00 44798.9986111111

These values look like Posix time, only in days. Is it right to assume that the Epoch used in Google Sheets is

1899-12-30 00:00:00 UTC ?

If so, why this choice?

Upvotes: 1

Views: 262

Answers (1)

Brooke
Brooke

Reputation: 372

You're correct that Google Sheets' epoch is December 30th, 1899. According to an archived Microsoft dev blog, this was due to a bug in Lotus 1-2-3, Excel's predecessor:

And finally, why 30 December 1899? Why not, say, 31 December 1899, or 1 January 1900 as the zero day? Actually, it turns out that this is to work around a bug in Lotus 1-2-3! The details are lost in the mists of time, but apparently Lotus 1-2-3 used this date format but their devs forgot that 1900 was not a leap year. Microsoft fixed this bug by moving day one back one day.

To be compatible with Excel, Google Sheets implemented the same feature.

Upvotes: 3

Related Questions