How Does Excel Store Dates?
- Jul 23
- 3 min read
When you type a date into Excel, it looks like a date on screen, but what's actually sitting inside that cell is something quite different.
Contents:
Quick Answer
Excel stores every date as a plain whole number, called a serial number, where 1 = January 1, 1900. Each day after that adds one to the count, so January 2, 1900 is 2, January 1, 2024 is 45292, and so on. The date format you see in the cell (1/1/2024, 01-Jan-24, etc.) is just a display setting layered on top of that number.
How the Date Serial Number System Works
Every date in Excel maps to a unique integer. January 1, 1900 is 1. December 31, 9999 is 2958465. That's the full range.
To see the number underneath any date cell, select the cell and format it as a Number (Home tab, Number format dropdown). The date display disappears and you see the raw serial number.
This is also why you can accidentally end up with a cell that shows a five-digit number when you expected a date. Excel has the right value stored, it just doesn't have the date format applied. Change the format back to a date and it displays correctly.
Times work the same way, but as decimals. 12:00 PM (noon) is 0.5, because it's half a day. A cell containing 1/14/2024 12:00 PM is actually storing 45305.5.
Why This Matters for Formulas
Because dates are numbers, date math is just arithmetic. To calculate the number of days between two dates, you subtract one from the other:
= B2 - A2If A2 is 1/1/2024 and B2 is 1/14/2024, the result is 13. No special function needed, just subtraction of two serial numbers.
It also means you can add days to a date with simple addition. To find the date 30 days from today:
= TODAY() + 30TODAY() returns today's serial number, and adding 30 gives you the serial number 30 days out. Format the result as a date and you're done.
This same logic is what powers more complex date functions like NETWORKDAYS and EOMONTH. They're all operating on integers under the hood. You can see this in practice in posts like how to calculate working days in a month.
The 1900 Leap Year Bug
Excel treats February 29, 1900 as a valid date (serial number 60), but 1900 was not actually a leap year. This is a known bug that was inherited from Lotus 1-2-3, the dominant spreadsheet software before Excel, and Microsoft kept it for compatibility reasons.
In practice this only matters if you're working with dates in early 1900, which is rare. For any date from March 1900 onward, every serial number is off by one compared to what a mathematically correct system would give you, but since every date is off by the same one, the difference between any two dates is still correct.
Dates Before 1900
Excel can't store dates before January 1, 1900 as true date values. If you type 12/31/1899 into a cell, Excel treats it as text, not a date. You can't do date arithmetic on it, and date functions won't recognize it.
If you need to work with historical dates before 1900, the usual workaround is to store the year, month, and day in separate numeric columns and handle the math manually. Excel's DATEDIF function also won't help here since it relies on the same serial number system.
Comments