Most Excel users think a date is a date is a date. They type 2024-03-15 into cell A1, format it as Mar 15, 2024, and assume Excel ‘understands’ it like a human would. It doesn’t. Not even close.
The Myth
People believe Excel stores dates as strings, calendar entities, or ISO-formatted values—something inherently temporal. That’s why they’re shocked when =A1+1 returns 45,365 instead of an error, or why copying a date from Excel into Notepad shows 45365. They assume Excel is ‘converting’ or ‘interpreting’—but no. Excel isn’t interpreting anything. It’s just displaying a number that happens to look like March 15, 2024—if you tell it to.
The Reality
Excel stores every date as a plain integer: the number of days since January 1, 1900 (the so-called ‘1900 date system’). January 1, 1900 = 1. January 2, 1900 = 2. December 31, 2023 = 45,289. March 15, 2024 = 45,365.
Time? That’s a decimal fraction. 12:00 PM on March 15, 2024 = 45365.5. 6:00 AM = 45365.25. Midnight = 45365.0.
| Date Input | Actual Stored Value | Cell Format Applied | What You See |
|---|---|---|---|
| 1/1/1900 | 1 | Short Date | 1/1/1900 |
| 2/29/1900 | 60 | Long Date | Tuesday, February 29, 1900 |
| 12/31/2023 | 45289 | Custom: dd-mmm-yyyy | 31-Dec-2023 |
| 3/15/2024 6:00 AM | 45365.25 | Custom: m/d/yyyy h:mm AM/PM | 3/15/2024 6:00 AM |
| 7/4/2025 | 45833 | General | 45833 |
| Sarah Chen’s hire date (Acme Corp) | 45120 | Short Date | 7/12/2023 |
| Q3 deadline (Nexus Labs) | 45231 | mmm dd, yyyy | Oct 03, 2023 |
Why the Myth Persists
Because Excel’s UI hides the underlying value. The ribbon shows ‘Date’ as a category—not ‘Number with fractional offset’. Help docs say ‘Excel stores dates as sequential serial numbers’, but bury it in footnote #3 of a 27-page ‘Date Functions’ overview. And most training videos start with ‘Just type the date and format it!’—never mentioning that TEXT(A1,"yyyy-mm-dd") is just string-wrapping a number.
The 1900 date system also has a famous bug: Excel incorrectly treats 1900 as a leap year. So February 29, 1900 exists in Excel—but not in reality. That’s why serial number 60 corresponds to a non-existent date. Microsoft kept it for backward compatibility with Lotus 1-2-3 in 1987. We’re still paying the price.
The Right Way
Stop thinking about ‘entering dates’. Start thinking about ‘entering numbers and applying date formatting’. Here’s how to do it deliberately:
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Type =DATE(2024,3,15) in B2 |
45365 appears (General format) | None needed |
| 2 | Select B2 → Home tab → Number group → click dropdown → choose ‘Short Date’ | Displays as 3/15/2024 | Alt + H, N, D |
| 3 | In C2, enter =B2+7 |
45372 → displays as 3/22/2024 (if formatted) | None needed |
| 4 | In D2, type 45365, then apply Short Date format |
Instantly becomes 3/15/2024 — no typing, no parsing | Ctrl + 1 → Number tab → Category → Date |
The beauty of this approach is that arithmetic becomes trivial. Need the first day of next month? =EOMONTH(A1,0)+1 works because EOMONTH returns a serial number—and adding 1 is just integer math. What makes this elegant is that you never need to parse text, handle time zones, or worry about locale-specific separators.
Surprising tip: You can store dates as negative numbers. Serial number 0 = December 31, 1899. -1 = December 30, 1899. Try it: type -1 in A1, apply Short Date format. You’ll see 12/30/1899. Excel doesn’t block it.
Proof It Works
Here’s what happens when you treat dates as numbers vs. treating them as strings:
| Scenario | ‘Myth’ Approach (Text/Formatted) | ‘Reality’ Approach (Serial Numbers) |
|---|---|---|
| Calculate days between two dates | =DAYS(DATEVALUE("2024-03-15"), DATEVALUE("2023-07-12")) — fails if source cells are text | =B2-A2 — works instantly, even if A2=45120 and B2=45365 |
| Add 30 business days | Complicated nested TEXT + WORKDAY formulas, often breaks on import | =WORKDAY(A2,30) — clean, reliable, accepts serials or formatted dates |
| Filter for ‘dates after Q2 2023’ | Must convert column to date first—risk of misinterpretation (e.g., 05/06/2023 = May 6 or June 5?) | Filter > Number Filters > Greater Than > 45100 — unambiguous, fast, locale-proof |
| Import CSV with raw serials | Excel auto-converts 45365 to 3/15/2024 only if it guesses right — often fails | Paste as values → apply Date format → done. No guessing, no errors. |
Exceptions
There *are* cases where the ‘myth’ holds up—and pretending dates are just numbers backfires:
- Pre-1900 dates: Excel can’t store them natively. Serial -1000 = 1897-04-06, but anything before Jan 1, 1900 requires workarounds (text + custom logic or Power Query).
- ISO 8601 timestamps with timezone offsets:
2024-03-15T14:30:00Zisn’t a serial number. Use Power Query or TEXTSPLIT + DATEVALUE + TIMEVALUE — but know you’re converting, not storing. - Legacy Mac Excel (1904 date system): Starts at Jan 1, 1904 = 1. If you open a file created on old Mac Excel, serials will be ~1,462 lower. Check File → Options → Advanced → ‘Use 1904 date system’.
So yes—Excel stores dates as numbers. Always has. Always will. But knowing that changes everything: from debugging a broken VLOOKUP on date columns, to writing bulletproof dashboards, to explaining to your intern why =A1="3/15/2024" returns FALSE even when A1 looks identical.
Next step: Open a blank workbook. In A1, type 45365. Press Ctrl + 1. Choose Category → Date → pick any format. Watch the magic—not of Excel understanding time, but of Excel revealing its quiet, consistent, numeric soul.
