Excel Date Showing as a Number? How to Fix It

When an Excel date is showing as a number, such as 45366 instead of 15 March 2024, nothing is broken. Excel stores every date as a number, and the cell is simply formatted to show the number instead of the date. This guide shows the two-click fix, explains why lookups and pasted values lose their date format, and shows how to keep dates readable when you join them to text.

Why Excel shows a date as a number#

Excel counts dates as days since 1 January 1900, which is day 1. This count is called the serial number. So 15 March 2024 is stored as 45366, and 16 March 2024 is 45367.

A number format decides how that value looks. With a date format, you see 15/03/2024 or 15-Mar-2024. With the General or Number format, you see 45366. The value underneath is the same either way.

That's also why you can subtract dates or add days to them: they're numbers. The article on why Excel dates are numbers explains the system in more detail.

How to fix a date showing as a number#

Change the cell's number format back to a date. You don't need a formula.

1. Select the cells with the numbers.
2. On the Home tab, open the Number Format box (it probably says General or Number).
3. Choose Short Date or Long Date.

The keyboard shortcut is Ctrl+Shift+# on Windows and Control+Shift+# on a Mac. It applies a date format with day, month and year.

For a specific style, such as 15-Mar-2024 or 2024-03-15, open Format Cells with Ctrl+1 (Mac: ⌘1), choose Date or Custom, and pick or type a format. The guide to changing the date format lists the codes, like dd-mmm-yyyy.

If you see ######## instead of a number or a date, the column is too narrow. Double-click the right edge of the column header to widen it. See why Excel shows hash symbols for more.

Why formulas return dates as numbers#

When you type a date or a simple formula such as =B2+30, Excel usually applies a date format for you. But many formulas return the date's value without its format:

• Lookups such as VLOOKUP, XLOOKUP and INDEX MATCH bring back 45366, not 15/03/2024.
• MAX, MIN, SUMIFS and MAXIFS return the latest or earliest date as a plain number.
• IF that returns a date in one case and text in another often lands in a General cell.

The fix is the same: format the result cells as a date. The answer is correct; only its look is wrong. If you work with date maths a lot, the guide to adding days, months and years shows which functions keep things tidy.

Why dates turn into numbers when you join them to text#

The & operator joins the stored values, not what you see. If B2 holds 15 March 2024:

="Due "&B2

This returns Due 45366. To control how the date looks inside text, wrap it in TEXT with a format code:

="Due "&TEXT(B2,"d mmm yyyy")

Now the result is Due 15 Mar 2024. Some useful format codes:

TEXT returns text, so you can't do date maths on the result. Keep the real date in its own cell and use TEXT only for labels. The same idea applies when a formula itself shows up as text in the cell; see why a formula shows as text.

Dates that became numbers after copy and paste#

Paste Values pastes the stored value without the format, so pasted dates show up as serial numbers. Two ways to avoid that:

• Use Paste Special > Values & Number Formats instead of plain Values. On Windows, press Ctrl+Alt+V; on a Mac, press Control+⌘+V.
• Or paste values first, then format the cells as a date.

Data from a CSV file or another system can also arrive as serial numbers. If the numbers are around 40000 to 50000, they're dates from roughly 2009 to 2036 and just need a date format.

The opposite problem: dates stored as text#

Sometimes the number format isn't the issue at all, because the "date" is really text. Changing the format does nothing, the dates sit on the left of the cell, and they sort in the wrong order. This happens a lot with imported data.

To check, type =ISNUMBER(B2). A real date returns TRUE, and a text date returns FALSE. To convert a text date that Excel can read, use =DATEVALUE(B2) and format the result as a date. If the day and month come out swapped, the text uses a different order from your computer's settings; see how to fix swapped days and months for formulas that rebuild the date safely.

Dates that are four years and one day off#

If the numbers turn into dates but they're all 4 years and 1 day early or late, the file uses a different date system. Excel has two: the 1900 system (the default on Windows and current Mac versions) and the 1904 system, which older Mac files sometimes use. The gap between them is 1,462 days.

On Windows, check File > Options > Advanced > When calculating this workbook > Use 1904 date system. On a Mac, go to Excel > Preferences > Calculation. Only change this if you understand the effect: it moves every date in the workbook. To fix only pasted dates, add or subtract 1462 instead.

Practise it#

Practise working with dates in a live spreadsheet: you'll enter dates, add days and see how serial numbers work, with each answer checked instantly. Then practise number and date formats so the right format is always one shortcut away. Try the TEXT exercise on this page first.