How to Change Date Settings in Excel: Formats, Systems, and What Controls Them

Dates in Excel are deceptively simple on the surface — but under the hood, they're governed by a layered system of regional settings, cell formatting, and date serial numbers. Knowing which layer to adjust makes the difference between a quick fix and hours of troubleshooting.

Why Excel Dates Behave the Way They Do

Excel doesn't store dates as text. It stores them as serial numbers — integers counting forward from a base date. By default, January 1, 1900 = 1, and every day after that increments by one. What you see in a cell — "01/15/2024" or "15-Jan-24" — is just a display format applied on top of that number.

This matters because:

• Changing a cell's format only changes how the date looks, not its underlying value
• Changing the date system (1900 vs. 1904) shifts every date in the workbook by four years
• Changing regional/locale settings affects the default date format Excel expects when you type

If your dates are showing as numbers, displaying in the wrong order (month/day vs. day/month), or refusing to parse correctly when entered, the fix lives in a different layer each time.

How to Change the Date Display Format in a Cell

This is the most common task — adjusting how an existing date appears.

1. Select the cell or range containing your dates
2. Press Ctrl + 1 (or right-click → Format Cells)
3. Go to the Number tab → select Date
4. Choose a built-in format from the list, or select Custom to enter your own

Common custom date format codes:

Custom formats give you full control over separators, abbreviations, and ordering — without touching any system-level settings.

How to Change the Regional Date Format Excel Uses by Default

If you want Excel to interpret dates you type differently — for example, defaulting to dd/mm/yyyy instead of mm/dd/yyyy — this is controlled at the operating system level, not inside Excel itself.

On Windows:

1. Open Control Panel → Region (or search "Region settings")
2. Under Formats, select your preferred region
3. Click Additional Settings → Date tab to customize short and long date formats
4. Restart Excel for changes to take effect

On Mac:

1. Go to System Settings → General → Language & Region
2. Adjust the Region or customize date format under Advanced

Excel inherits this setting. Changing it affects how Excel parses typed dates and what format new date cells default to — but it does not retroactively reformat dates already in your spreadsheet.

Switching Between the 1900 and 1904 Date Systems 📅

Excel supports two date serial systems:

• 1900 date system (default on Windows): Day 1 = January 1, 1900
• 1904 date system (legacy default on Mac): Day 1 = January 1, 1904

The 1904 system was originally used on early Macs. Today both platforms default to 1900, but you may encounter workbooks — especially older Mac-originated files — using the 1904 system.

To check or change this:

1. Go to File → Options → Advanced (on Windows)
2. Scroll to When calculating this workbook
3. Check or uncheck Use 1904 date system

⚠️ Caution: Switching date systems on a workbook that already contains date values will shift every date by exactly 1,462 days (four years and one day). This can silently corrupt your data if you're not expecting it. Only change this setting intentionally, and verify dates afterward.

When Dates Show as Numbers Instead of Dates

If a cell displays something like 45306 instead of a date, the cell is formatted as General or Number — the serial number is there, the display format isn't.

Fix: Select the cell → Format Cells → Date → choose your format.

If typed dates aren't being recognized as dates at all (they stay left-aligned as text), Excel is treating them as strings. This typically happens when:

• The date format you typed doesn't match the regional locale Excel expects
• The cell was pre-formatted as Text before you typed
• You imported data with dates as plain text strings

In these cases, you may need to use Text to Columns, the DATEVALUE() function, or Power Query to parse the text into real date values.

The Variables That Determine Which Fix You Need

How you change date settings in Excel depends on several factors that vary by user and setup:

• Operating system and locale — Windows and Mac handle regional date inheritance differently
• Excel version — The 365/2021 interface differs slightly from Excel 2016 or 2019, particularly in the Options menu layout
• Data source — Dates typed manually behave differently than dates imported from CSV, databases, or APIs
• Workbook origin — Files shared across regions or created on different OS versions may carry conflicting date assumptions
• Whether you need display changes or parsing changes — these are solved in completely different places

A date that looks wrong in one context might be correctly stored but poorly formatted, or correctly formatted but incorrectly parsed — and distinguishing between those two problems is what determines where to start.