Excel dates showing as numbers? Check Show Formulas
If dates such as 21/06/2026, 11/12/2025 or similar suddenly show up as numbers in Excel, the problem may not be the date format. It may be that Show Formulas has been selected.
This is a classic Excel annoyance. One minute your spreadsheet looks normal. The next, dates look like random numbers and the sheet appears to have had a small administrative breakdown. Before you start reformatting cells, changing regional settings, or accusing Excel of being ridiculous again, check whether Excel is showing formulas instead of calculated results.
The clue is usually that the number looks too large to be a normal value. You might type a date and see something like 46002. That is not Excel inventing a mystery code. It is the date serial number that Excel uses behind the scenes.
Why dates appear as numbers in Excel
Excel stores dates as serial numbers. This is how Excel can calculate with dates. For example, it can work out the number of days between two dates, add 30 days to an invoice date, calculate deadlines, or sort records chronologically.
Normally, you do not see the serial number. Excel applies a date format and displays the value in a way that makes sense to humans, such as 11/12/2025. Underneath, Excel still stores the date as a number.
That hidden number becomes visible when the worksheet is set to show formulas instead of results. This can make a normal date appear to turn into a number such as 46002.
The important point is this: the date may not be broken. The value may still be a perfectly valid date. Excel is just displaying it in an unhelpful way.
The most likely cause: Show Formulas is switched on
The Show Formulas option is useful when you want to audit a spreadsheet. It allows you to see formulas directly in cells rather than seeing the calculated answers. Unfortunately, it can also make a spreadsheet look wrong if it has been switched on accidentally.
When Show Formulas is on, Excel may show the underlying values in cells rather than the normal formatted results. This is why a date can appear as a serial number even though the formula bar still shows the correct date.
In the example below, the formula bar shows the real date value, but the cell displays the date as its serial number. The fix is not to retype the date. The fix is to turn off Show Formulas.
How to tell if this is the problem
Before changing anything, look for these signs:
- The formula bar still shows the correct date.
- The cell shows a number instead of the formatted date.
- The problem affects more than one cell or more than one formula result.
- The worksheet columns may look wider than usual because formulas or underlying values are being displayed.
- The Show Formulas button on the Formulas tab appears selected.
If several of these are true, do not start rebuilding the spreadsheet. It is probably just a display setting.
There are two ways to fix it
You can fix this quickly from the ribbon, or you can use Excel Options if you want to check the setting for a specific worksheet.
Option 1: Quick fix for the active worksheet
To quickly fix the sheet you are currently working on, go to:
Formulas > Formula Auditing > Show Formulas
If Show Formulas is selected, click it again to turn it off. Your dates should return to their normal formatted date display.
This is the fastest fix and should usually be the first thing you try. It is especially useful if the spreadsheet looked normal before and has suddenly started showing strange numbers or formulas.
Keyboard shortcut
You can also toggle Show Formulas using the keyboard shortcut:
Ctrl + `
The key is usually the grave accent key, found near the top-left of the keyboard, often under Esc. If you press this shortcut by mistake, Excel can suddenly switch into formula display mode. Pressing it again usually switches the worksheet back to normal.
Option 2: Fix the worksheet setting in Excel Options
If the ribbon button does not solve it, or if you want to check the setting for a particular worksheet, use Excel Options.
Go to:
File > Options > Advanced
Scroll down to Display options for this worksheet. In that section, choose the affected worksheet from the dropdown, then untick:
Show formulas in cells instead of their calculated results
This setting is useful because it allows you to check whether the issue is affecting a specific worksheet. One sheet in a workbook may be set to show formulas while another sheet displays results normally.
Why reformatting the cell may not fix it
The obvious reaction is to select the affected cells and apply a date format. Sometimes that works, but not always. If Show Formulas is the real cause, changing the date format may appear to do nothing.
That is because the issue is not necessarily the format of the date. It is the way the worksheet is being displayed. Excel is being told to show formulas or underlying values rather than the calculated, formatted results.
So if date formatting does not work, do not keep applying more formats. Check Show Formulas instead.
What Excel date serial numbers mean
Excel dates are stored as numbers so that they can be used in calculations. Each whole number represents a day. This is why dates can be added, subtracted and compared.
For example, if a date is stored as a number, Excel can easily calculate:
- how many days have passed since a start date;
- how many days remain before a deadline;
- whether a payment date is overdue;
- which date comes first when sorting a list;
- the number of working days between two dates.
This is extremely useful, but it does mean that when Excel exposes the raw value, the date can look like a meaningless number.
Common places this problem appears
This issue often appears in ordinary admin spreadsheets, especially where dates are used for tracking tasks, payments, attendance, deadlines or records.
Examples include:
- payment dates showing as serial numbers;
- student attendance dates appearing as large numbers;
- project deadlines no longer showing as formatted dates;
- invoice dates looking like random numeric codes;
- HR or payroll spreadsheets showing date fields incorrectly.
Because the numbers look so unlike dates, it is easy to assume the data has been corrupted. In many cases, it has not. Excel is simply showing the wrong view.
What to check if Show Formulas is not the cause
If Show Formulas is not switched on, there are a few other possible causes.
The cell may be formatted as General or Number
If a date cell is formatted as General or Number, Excel may show the date serial number. Select the cell, go to the Home tab, and change the number format to Short Date, Long Date or a custom date format.
The value may be text, not a real date
Sometimes a date has been imported as text. This can happen when data comes from another system, a CSV file, a web export, or a badly formatted spreadsheet. If Excel sees the value as text, date formatting may not behave as expected.
The regional date format may be different
Dates can also behave strangely when the spreadsheet uses one date format and your computer expects another. For example, 03/04/2026 could mean 3 April or 4 March depending on the regional settings.
The workbook may use the 1904 date system
Excel also has a workbook setting called the 1904 date system. This is less commonly the cause, but it can affect date calculations if a workbook has been moved between systems or copied from another source.
Quick checklist
- If dates show as numbers, first check Show Formulas.
- Use Formulas > Formula Auditing > Show Formulas for a fast worksheet fix.
- Use File > Options > Advanced to check the setting for a specific worksheet.
- Look at the formula bar. If it shows the correct date, the value may still be fine.
- Do not immediately start changing date formats. That may not be the problem.
- If Show Formulas is off, then check the cell format, imported text values and regional date settings.
Frequently asked questions
Why does Excel turn a date into a number?
Excel has not really turned the date into a number. Excel stores dates as numbers already. Usually it applies a date format so that you see a normal date. If that formatting or display behaviour changes, you may see the underlying serial number instead.
Why does the formula bar show the date but the cell shows a number?
This is a strong sign that the underlying value is still a date, but the worksheet display is not showing the formatted result. Check whether Show Formulas is switched on.
Will turning off Show Formulas change my data?
No. Turning off Show Formulas changes how the worksheet is displayed. It does not delete formulas, remove data or change the underlying date values.
Can Show Formulas affect only one worksheet?
Yes. The setting can apply to a specific worksheet. That is why the Display options for this worksheet section in Excel Options includes a worksheet dropdown.
What should I do if the date still shows as a number?
If Show Formulas is off and the date still appears as a number, select the cell and apply a date format from the Home tab. If that still does not work, the value may have been imported as text or may need converting into a real Excel date.
Summary
When Excel dates appear as numbers, the first thing to check is Show Formulas. It is a simple setting, but it can make a spreadsheet look as if all the dates have gone wrong.
Try the quick ribbon fix first:
Formulas > Formula Auditing > Show Formulas
If needed, check the worksheet-specific option under:
File > Options > Advanced > Display options for this worksheet
Only after that should you start investigating date formats, imported text values or regional settings.