Excel dates are numbers: why yours turned into 45658
A date is the number of days since 1900. That one fact explains the number in your cell, the 03/01/1900 subtraction, and every text date from an export.
A date in Excel is a number. 1 January 2025 is the number 45658. It is the number of days since 1 January 1900.
Everything confusing about dates in Excel comes from that one sentence, so it is worth reading twice. The cell holds a number. The date you see is a costume the number is wearing.
| Serial number | Date | What it is |
|---|---|---|
| 1 | 1 January 1900 | Day one |
| 45658 | 1 January 2025 | An ordinary date |
| 45658.5 | 1 January 2025, midday | Half a day added |
| 45658.75 | 1 January 2025, 18:00 | Three quarters of a day |
Time is the part after the decimal point. Half a day is 0.5, so midday is .5. An hour is 1/24. A date with no time is a whole number. So a date and a date-with-time never look equal to Excel, even on the same day.
"My date turned into 45658"
Nothing was lost. The cell stopped being formatted as a date and went back to showing what it actually holds.
Select the cell. Press Ctrl+Shift+3. That is the shortcut for date format. The date comes back.
This happens most often after a paste, after a formula result, and after Text to Columns. None of them damaged anything.
"I typed 5-3 and Excel gave me a date"
The same thing in reverse. Excel guesses that something shaped like a date is a date. It converts it to a number and formats the cell to match. After that the cell is a date cell. Every number you type into it comes out as a date.
To get a plain number back, set the format to General first, then retype the value.
To stop it happening at all, format the column as Text before typing. Product codes like 1-2 and 05-06 are what this usually happens to.
"My subtraction says 03/01/1900"
This one catches everybody. Subtract two dates and the answer is a number of days. But Excel keeps the date format from the cells you subtracted. So it shows that number as a date.
=B1-A1 gives 2, and shows it as 2 January 1900. Because the
second day of 1900 is the number 2.
Fix: format the answer cell as General or Number. The value was right the whole time.
Arithmetic you can do because it is a number
-
Days between two dates:
=B1-A1 -
30 days from now:
=TODAY()+30 -
Is it overdue?
=IF(A1<TODAY(),"Overdue","OK") -
Hours between two times:
=(B1-A1)*24, formatted as a number
TODAY() changes every time the file recalculates. So does NOW(). If you need the date something happened, type it. A formula moves it to today, and says nothing.
Months and years need a function
Days are easy because days are the unit. Months are not all the same length, so subtracting does not work.
=DATEDIF(A1,B1,"Y") — whole years between two dates. This is how
you calculate an age.
=DATEDIF(A1,B1,"M") — whole months.
DATEDIF is a leftover from Lotus 1-2-3. Excel will not offer it while you type. It is not in
the function list. And it has worked in every version for thirty years. The start date must
come first, or it returns #NUM!.
=EOMONTH(A1,0) gives the last day of that month.
=EOMONTH(A1,1) gives the last day of the next one. It is the
quickest way to build a month-end column.
Working days, not days
=NETWORKDAYS(A1,B1) counts the days between two dates and skips
Saturdays and Sundays.
=WORKDAY(A1,10) gives the date 10 working days after A1. Both take an optional list of holiday dates as a last argument. A delivery date can then skip a public holiday.
The dates that are not dates
Dates from an export are usually text. They look identical to real dates and no arithmetic works on them.
The fastest test is alignment. Excel puts numbers, including dates, on the right of the cell. It puts text on the left. A column of dates hugging the left edge is a column of text.
One cell: =DATEVALUE(A1), then format as a
date.
A column: select it. Then Data → Text to Columns → Next → Next → Date. Choose the order the text uses, such as DMY. This changes the cells themselves and takes five seconds.
Two oddities to know about
Excel thinks 1900 was a leap year. It was not. Serial number 60 is 29 February 1900, a day that never existed. This was copied from Lotus 1-2-3 in the 1980s. It was kept on purpose so old files still added up. It only matters for dates before March 1900.
Some old Mac files count from 1904. Open one alongside a normal file and every date is out by four years and a day. It is a setting, under Options, and changing it moves every date in the file.
Questions people ask
Why does my date show as ###?
The column is too narrow. Excel will shrink text to fit and it will not shrink a number. Double click the line between the column letters to widen it.
Why do two dates that look the same not match?
One of them has a time on it. 1 January is 45658 and 1 January at 09:00 is 45658.375. Compare
with =INT(A1)=INT(B1), which throws the time away.
How do I get just the year out of a date?
=YEAR(A1). There is a MONTH and a DAY too. To group a report by
month, use =TEXT(A1,"yyyy-mm"), which sorts correctly because the
year comes first.
Is 1 January 1900 really the earliest date?
In a cell, yes. Excel cannot store an earlier date as a number. Dates before 1900 have to be kept as text, and no arithmetic works on them.