Why Excel Stores Dates as Numbers (and Why It Matters)
By ExcelReady TeamUpdated 5 min read
The short answer
Excel stores every date as a number called a serial number: the count of days since 1 January 1900, which is day 1. So 1 January 2025 is 45658. The date format only changes how that number looks. Because dates are numbers, you can subtract them to get the days between, add days, sort them and compare them.
Key takeaways
- A date is a whole number of days: 1 January 1900 is 1 and 1 January 2025 is 45658.
- A time is a fraction of a day: 0.5 is 12:00 noon.
- Ctrl+Shift+~ shows the number behind a date; Ctrl+Shift+# turns it back into a date (Control on a Mac).
- Later date minus earlier date gives the days between them. A date plus 7 is one week later.
- A date stored as text looks the same but can't be used in date math. ISNUMBER shows FALSE for it.
On this page+
Excel stores every date as a whole number: the count of days since 1 January 1900. A date format only makes that number look like a date. Once you see Excel dates as numbers, a lot of strange behaviour makes sense, like a date that suddenly shows as 45658, or a "date" that won't subtract. This guide explains how the system works, what it lets you do, and how to fix the problems it causes. (Dates here are written month/day/year, the US style.)
How Excel stores dates as serial numbers#
Each date is a serial number: 1 January 1900 is day 1, 2 January 1900 is day 2, and so on. The number counts up by one every day.
| Date | Serial number |
|---|---|
| 1 January 1900 | 1 |
| 1 January 2008 | 39448 |
| 8 January 2024 | 45299 |
| 1 January 2025 | 45658 |
| 6 January 2025 | 45663 |
The date format decides how the number is shown: as 1/6/2025, 6-Jan-25 or 6 January 2025. Change the format and the look changes, but the number underneath stays 45663.
Storing dates as numbers is what makes date math simple. Excel doesn't need special rules for "days between two dates". It just subtracts.
Times are fractions of a day#
A time is the part of a day that has passed, stored as a decimal. 0.5 is 12:00 noon, 0.25 is 6:00 AM and 0.75 is 6:00 PM.
A date with a time is a whole number plus a fraction. 45658.75 is 1 January 2025 at 6:00 PM. This is the difference between TODAY, which returns a whole number, and NOW, which includes the time. See TODAY vs NOW for when to use each.
How to see Excel dates as numbers (and back)#
To see the number behind a date, select the cell and press Ctrl+Shift+~ (⌃⇧~ on a Mac) to switch it to General, Excel's plain format. The date turns into its serial number. To turn it back, press Ctrl+Shift+# (⌃⇧#).
On a Mac, use Control, not Command, for both. (~ is Shift plus the key to the left of 1, and # is Shift+3.) Nothing about the value changes either way; only the look does.
What you can do because dates are numbers#
Because a date is a number, ordinary math works on it. Say B2 holds a start date (1/6/2025) and C2 an end date (1/31/2025):
| Task | Formula | Result |
|---|---|---|
| Days between two dates | =C2-B2 | 25 |
| A date 30 days later | =B2+30 | 2/5/2025 |
| Is the end after the start? | =C2>B2 | TRUE |
| Full months between them | =DATEDIF(B2,C2,"M") | 0 |
| Full days, with DATEDIF | =DATEDIF(B2,C2,"D") | 25 |
Sorting works too: oldest to newest is just smallest to largest. DATEDIF counts whole days, months or years between two dates. The start date goes first and the unit goes last, in quotes. See the DATEDIF guide for every unit, and days between dates for more examples.
Formulas like these use cell references to point at the dates, so they update when a date changes. Excel cell references explained covers how they work.
Why a date shows as a number, or a number as a date#
When a cell shows the "wrong" thing, the cause is almost always its format, not its value:
- A date shows as 45658. The cell has the General or Number format. Press Ctrl+Shift+# (⌃⇧#) to show it as a date.
- A small number shows as a date in 1900. Someone gave the cell a date format. 14 with a date format shows as 1/14/1900. Press Ctrl+Shift+~ (⌃⇧~) to reset it.
- Days between two dates shows as a date. Excel sometimes copies the date format into the result of
=C2-B2. The answer is right; switch the cell to General to see 25.
If you change a format by mistake, press Ctrl+Z (⌘Z) to undo it. Edit, clear, undo and redo has more keys for fixing mistakes.
Dates stored as text: the real problem#
A date stored as text looks like a date but isn't a number, so date math doesn't work on it. This often happens with data copied from websites or exported from other systems.
Signs of a text date:
- It sits on the left of the cell. Real dates (numbers) sit on the right by default.
=ISNUMBER(A2)returns FALSE.- Subtracting it gives #VALUE!, or sorting puts "10/1/2025" before "2/1/2025".
Joining parts with & also creates text: =C2&"/"&D2&"/"&B2 looks like a date but isn't one. Build real dates with DATE instead: =DATE(2025,1,6) returns the serial number 45663. The date functions guide covers DATE, YEAR, MONTH and DAY.
Watch your region, too. Excel reads typed dates using your computer's settings, so 1/6/2025 is 6 January in the US but 1 June in the UK.
The 1900 and 1904 date systems#
Excel has two date systems. The 1900 system, where day 1 is 1 January 1900, is the default in Excel for Windows and in current Excel for Mac. Early versions of Excel for Mac used the 1904 system, which starts counting from 1 January 1904.
The two systems are 1,462 days apart. If you copy dates between a 1900 workbook and a 1904 workbook, they can shift by about four years. The setting is in Excel's options, and most files you meet today use 1900.
Two more limits to know: Excel can't store dates before 1 January 1900 as real dates (they stay as text), and the 1900 system treats 1900 as a leap year, a choice kept for compatibility with older spreadsheet programs. Neither affects modern dates.
Practise working with dates#
Seeing it for yourself is the quickest way to understand it. In the how Excel stores dates lesson, you build dates with DATE, switch them to General to see their serial numbers, and take them apart with YEAR, MONTH and DAY. The more formatting lesson has you apply a date format and fix a number that shows as a 1900 date. To jump to a date column fast in a big sheet, try the Name Box.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Dates are numbers, so you can subtract them. In D2, find how many days the website update took: type =C2-B2 and press Enter. The later date goes first.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Days | ||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 |
Tip: Put the end date first, then a minus sign, then the start date.
Practise this in a lesson
Frequently asked questions
Why does Excel show my date as a number like 45658?
The cell has the General or Number format, so Excel shows the serial number behind the date. Select the cell and press Ctrl+Shift+# (Control+Shift+# on a Mac) to show it as a date again.
What date is serial number 1 in Excel?
In the 1900 date system, which Excel for Windows and current Excel for Mac use by default, serial number 1 is 1 January 1900.
How do I calculate the days between two dates in Excel?
Subtract the earlier date from the later one, for example =C2-B2. If the answer looks like a date, change the cell's format to General to see the number of days.
How can I tell if a date is stored as text?
Type =ISNUMBER(A2) in an empty cell. TRUE means A2 holds a real date (a number); FALSE means it is text. Text dates also sit on the left of the cell by default.
What is the 1904 date system?
It is a second date system that counts days from 1 January 1904. Early versions of Excel for Mac used it by default. Dates in a 1904 workbook are 1,462 days apart from the same serial numbers in the 1900 system.