I’m working with date and time data in Google Sheets and noticed something odd. When I copy datetime values and paste them using Ctrl+Shift+V (paste special - values only), they appear as decimal numbers instead of dates.
Here’s what I’m seeing:
Original DateTime
Pasted as Number
12/03/2018 14:30:00
43166.6041666667
08/07/2020 14:30:00
44020.6041666667
16/12/2023 14:30:00
45281.6041666667
These decimal values seem to represent days since some starting point. I’m trying to figure out what reference date Google Sheets uses for this numbering system.
Is it correct that Google Sheets counts days from December 30, 1899 as day zero? This would mean that’s the epoch or base date for the serial number system.
Why would Google choose this particular starting date instead of something more common like January 1, 1900 or Unix epoch time?
I’ve hit this problem tons of times migrating data between systems. What surprised me at first - that December 30, 1899 date isn’t random. It comes from old spreadsheet apps in the 1980s. Here’s how these serial numbers work: the whole number part is complete days, the decimal part is the time fraction. So 43166.6041666667 means 43166 full days from the base date, plus about 14.5 hours into that day. Pro tip: these raw numbers are actually great for calculations since you can just add or subtract whole numbers to get different days.
The December 30, 1899 base date is a historical quirk stemming from Lotus 1-2-3. Microsoft retained it in Excel for consistency, and Google Sheets followed suit to ensure compatibility across platforms. Interestingly, this system inaccurately considers 1900 a leap year, which results in a phantom February 29, 1900 being part of the date system. Despite its peculiarity, you can easily convert these serial numbers back to recognizable dates with regular formatting or by employing DATE() functions if you need to manipulate the raw numbers.
yep, exactly! google sheets uses day 0 like excel - dec 30, 1899. seems random but it’s for compatibility with old stuff like lotus 1-2-3. the decimal part shows time - .6041666667 is 14:30 (2:30pm).