SOLVED: Why Does Excel Sometimes Show Numbers Like 45731 For Date Fields

If you change the format of a cell from DATE to pretty much anything else, the date will be converted to a number, like 45731. You might as what that is. That date is the number of days since January 1, 1900.

Here is the detailed explanation:

1. Excel’s Date System (Epoch):
   • Excel stores dates as serial numbers, where each integer represents a day
   • The starting point (or “epoch”) for Excel’s date system is January 1, 1900
   • This makes the number 1 in Excel corresponds to January 1, 1900. The number 2 is January 2, 1900, and so on
2. The “1900 Leap Year Bug”:
   • This is a historical anomaly that Microsoft inherited from Lotus 1-2-3 (a very popular spreadsheet program before Excel)
   • Excel incorrectly treats the year 1900 as a leap year but 1900 was not a leap year (it is a century year not divisible by 400)
   • This means that Excel adds an extra day into its calendar sequence for February 29, 1900, which never existed
   • RESULT: For any date after February 28, 1900, the Excel serial number will be one day ahead of what it should be if 1900 were correctly identified as a non-leap year.
3. The Conversion Logic (as implemented in the code):
   • To accurately convert an Excel serial number to a correct date, you need to account for this bug
   • If we simply added the Excel serial number directly to a base date of December 31, 1899 (so that January 1, 1900 is ‘1’), dates after February 28, 1900, would be off by one day
   • Instead, the common approach in programming (like Python) is to use December 30, 1899, as the effective base date
     • Why December 30, 1899? Because if you add 1 day to December 30, 1899, you get December 31, 1899. If you add 2 days, you get January 1, 1900
     • This effectively means that the Excel serial number N maps directly to N days after December 30, 1899, which correctly compensates for the non-existent February 29, 1900
   • In simpler terms:
     • We start from datetime(1899, 12, 30)
     • We then add excel_date_number of timedelta(days) to this base date
     • This calculation inherently corrects for the 1900 leap year bug for all dates after February 28, 1900
     • In our example the number 45731 is January 12, 2025

This method ensures that the converted date precisely matches what you would see in an Excel spreadsheet for the given serial number.