Why Is Serial Number 1 Dated December 31, 1899?

When converting a date to a number in Excel, Google Sheets or Tableau, you'll notice that the value 1 represents a specific point in time. In Excel, that number corresponds to January 1, 1900, but in Google Sheets and Tableau, it points to December 31, 1899. Why do the same numbers return different dates?

Dates are stored as numbers

Spreadsheets convert dates into serial numbers, which represent the number of days since a starting point. This allows for easy date calculations such as filtering, sorting, and time intervals.

Here’s how it works:

• Excel: 1 = 1900-01-01, 2 = 1900-01-02
• Google Sheets: 1 = 1899-12-31, 2 = 1900-01-01

The discrepancy lies in how each system handles a non-existent date: February 29, 1900.

Excel: Carrying forward a legacy bug

Excel treats 1900 as a leap year, even though it wasn’t. This was done intentionally to maintain compatibility with an older spreadsheet program, Lotus 1-2-3, which made the same mistake.

As a result, Excel includes February 29, 1900, even though it never existed. This adds an extra day to the calendar, and shifts all serial numbers before March 1, 1900 by one.

Google Sheets: Based on accurate calendar logic

Google Sheets corrects this historical inaccuracy. It does not treat 1900 as a leap year and follows the actual Gregorian calendar. Therefore:

• 60 = 1900-02-28
• 61 = 1900-03-01

Because it doesn’t include the phantom February 29, 1900, the date system starts one day earlier: December 31, 1899.