Excel Date Format Not Working? Here Is Why and How to Fix It

How Excel Stores Dates (The Root of All Date Problems)

Excel does not store dates as text like "01/06/2025". It stores them as serial numbers — the number of days since January 1, 1900. June 1, 2025 is stored as the number 45779. The "date format" is just a display instruction that tells Excel how to show that number.

This means there are two completely different types of date problems:

• Real date, wrong display — the cell contains a valid serial number but is formatted incorrectly
• Text date, no serial number — the cell contains a text string like "01/06/2025" that looks like a date but is not one, so Excel cannot format or calculate it

The fix is completely different for each type. Read on to identify which one you have.

Problem 1: Date Shows as a Number (e.g. 45779)

This happens when a real date serial number is formatted as a general number instead of a date format.

Fix:

1. Select the cells showing the number
2. Press Ctrl+1 to open Format Cells
3. Click the Number tab → select Date
4. Choose your preferred date format from the list and click OK

The number instantly becomes a properly formatted date. No data was lost — the underlying serial number was always a date, it was just displayed as a number.

Problem 2: Date Shows the Wrong Year (e.g. 1900)

If a date shows as a date in 1900, the serial number is very small — usually because a formula returned 0 or a very low number, and Excel is displaying that as a date near January 1, 1900.

Fix: Check what formula produced the value. A result of 0 displayed as a date shows as "00/01/1900". Use IFERROR to catch zero results and display a blank instead of a 1900 date:

=IFERROR(IF(your_formula=0, "", your_formula), "")

If the date is correct but showing the wrong year due to a regional settings issue, see Problem 4 below.

Problem 3: Date Is Stored as Text (Cannot Format or Calculate)

If your date came from a CSV export, a database, or a web import, it is very likely stored as a text string rather than a real date. Text dates cannot be formatted with date formats, and they cause #VALUE! in date calculations.

How to check: Select the cell. If the Number Format box shows "Text" or "General" and changing it to a Date format shows a large number (not a formatted date), the cell contains a text date.

Fix 1 — DATEVALUE():

=DATEVALUE("01/06/2025")

DATEVALUE converts a text date string to a real serial number. Then format the result as a date.

Fix 2 — Text to Columns: Select the column → Data → Text to Columns → Finish. Excel re-parses the column and often recognises text dates as real dates.

Fix 3 — DATE() with MID/LEFT/RIGHT: For consistently formatted text dates (e.g., always "YYYY-MM-DD"), extract the parts and rebuild as a real date:

=DATE(LEFT(A1,4), MID(A1,6,2), RIGHT(A1,2))

Problem 4: Day and Month Are Swapped (Regional Settings)

Excel interprets dates based on your computer's regional date format settings. If your computer is set to MM/DD/YYYY (US format) but the data is in DD/MM/YYYY (UK/European format), Excel reads 05/06/2025 as May 6 rather than June 5 — or rejects it as a text date entirely if the day exceeds 12.

Fix for text dates with swapped day/month:

=DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2))

This rebuilds the date by extracting day, month, and year explicitly from their correct positions in the text string — regardless of what regional settings think.

Long-term fix: Change Windows regional settings to match the date format of your data source (Control Panel → Region → Short date format), or ask the data provider to export in ISO format (YYYY-MM-DD) which Excel always reads correctly regardless of regional settings.

Problem 5: Date Format Changes When File Is Shared

If you create a file with dates formatted as "DD/MM/YYYY" and a colleague opens it on a computer with US regional settings, the dates may display differently — or worse, real dates may be misinterpreted.

Fix: Use a date format that includes the month name to make the date unambiguous: DD-MMM-YYYY (e.g., "01-Jun-2025"). This displays the same regardless of regional settings.

To apply this format: select the date cells → Ctrl+1 → Number → Custom → type DD-MMM-YYYY → OK.

Date Formula Quick Reference

Frequently Asked Questions

Why do my dates change when I save as CSV?

CSV files store dates as text strings, not Excel serial numbers. When you save as CSV and reopen, Excel has to re-interpret the text dates based on your regional settings — which may read them differently than how they were originally entered. Use XLSX format to preserve proper date serial numbers across saves.

How do I enter a date that does not change with TODAY()?

Type the date directly in the cell (e.g., 06/01/2025) rather than using the TODAY() function. Or press Ctrl+; to insert today's date as a static value that will not update tomorrow.

How do I check if a cell contains a real date or a text date?

Use =ISNUMBER(A1). Real dates are stored as numbers, so ISNUMBER returns TRUE. Text dates return FALSE. Alternatively, use =CELL("type", A1) — "v" means value (real date or number), "l" means label (text).