Let me guess – you just opened a spreadsheet and saw dates looking like 43526 instead of "March 15, 2023"? Or maybe your US colleague sent a file where 05/04/2023 means May 4th but your UK Excel thinks it's April 5th? Been there, ripped my hair out over that. Last quarter, I wasted three hours fixing export dates before a client meeting because Excel kept autocorrecting formats. Worst Monday ever.
Changing date formats shouldn't be this hard. But whether you're using Excel for Windows, Mac, or the web version, Microsoft makes you jump through hoops. This guide cuts the fluff and gives you every practical method to change date formatting in Excel, with real-life troubleshooting from my 10 years of spreadsheet battles.
Why Excel Dates Go Rogue (And How to Tame Them)
Before we fix dates, understand why this happens. Excel secretly stores dates as numbers ("serial numbers"). January 1, 1900 is number 1. Today is about 45,000+. The display format is just makeup applied to that number.
My golden rule: Always check if dates are true dates (right-aligned, numeric) or text dressed as dates (left-aligned). Text won't format correctly until converted.
Method 1: The Format Cells Shortcut (Fastest Fix)
Best for quick visual changes when data is already proper dates:
| Action | Steps | Works On |
|---|---|---|
| Keyboard Shortcut | Select cells → Press Ctrl+1 | Windows Excel |
| Ribbon Method | Home tab → Number group → Dropdown selector | All Excel versions |
| Right-Click | Right-click cells → Format Cells | All platforms |
In the Format Cells dialog (Number tab):
- Choose Date in category list
- Pick format from right pane (e.g. March 14, 2023)
- For custom formats, select Custom and type codes like:
- dd/mm/yyyy → 15/03/2023
- mmm-dd-yyyy → Mar-15-2023
I use this daily but hate that it doesn't change underlying data. If you sort or calculate, Excel still uses its internal number.
Method 2: TEXT Function for Permanent Conversion
When you need to permanently change date format in Excel for exports or systems:
- Formula: =TEXT(A1,"yyyy-mm-dd")
- Replace "A1" with your date cell
- Format codes same as Custom formatting
| Use Case | Formula Example | Output |
|---|---|---|
| Database export | =TEXT(B2,"yyyymmdd") | 20230315 |
| Readable reports | =TEXT(C3,"dddd, mmmm d") | Wednesday, March 15 |
| Locale-neutral | =TEXT(D4,"yyyy-mm-dd") | 2023-03-15 (ISO format) |
Warning: Output is text, not a real date. Can't use in calculations. Annoying limitation.
Method 3: Text-to-Columns for Stubborn Text Dates
When dates are trapped as text (green triangle error):
- Select date column
- Data tab → Text to Columns
- Choose Delimited → Next
- Uncheck all delimiters → Next
- Under Column data format: Select Date
- Choose format matching your data (DMY, MDY, etc)
This saved me last month with a CSV from our French office. Dates were "14/03/2023" but Excel refused to recognize them. Text-to-Columns forced conversion in 30 seconds.
Critical: Choose the correct date order in Step 6. Wrong order swaps day/month. I've made this mistake – turned Q1 reports into nonsense.
Advanced Tactics for Power Users
Power Query for Bulk Transformation
When changing date formats across multiple files:
- Data tab → Get & Transform Data
- Load data into Power Query
- Right-click date column → Change Type → Using Locale
- Select both data format and region
Why I prefer this: Creates reusable workflow. Changed 12 monthly reports in one go last audit season.
VBA Macro for Format Automation
For recurring tasks:
Sub ChangeDateFormat()
Selection.NumberFormat = "dd-mmm-yyyy"
End Sub
Bind to shortcut key. Saves hundreds of clicks if you format reports daily. But honestly? Only worth it if you do this constantly.
Regional Date Disasters and How to Avoid Them
Excel uses your system's locale settings. My team learned this painfully:
- US Excel: Month/Day/Year
- UK Excel: Day/Month/Year
- Sweden: Year-Month-Day
| Situation | Risk | Solution |
|---|---|---|
| Sending files internationally | 05/04/2023 = May 4 or April 5? | Use TEXT function with unambiguous format |
| Opening CSV files | Excel auto-converts to local format | Import via Data tab instead of direct open |
| Database exports | Formulas break if date formats mismatch | Standardize on ISO 8601 (YYYY-MM-DD) |
Fixing Specific Date Nightmares
Dates Showing as Numbers
Seeing 43526 instead of a date?
- Apply Date format (Ctrl+1)
- If stays numeric: Data is numbers, not dates. Use formula: =DATE(1900,1,1)+A1-2 (adjust for Mac's 1904 date system)
Year Shows as 1900 or 1905
Usually means you have day/month swapped:
- Text-to-Columns with correct date order
- Formula fix: =DATE(YEAR(A1),DAY(A1),MONTH(A1))
Dates Won't Sort Correctly
Classic sign of text dates mixed with real dates:
- Select column → Data → Sort → Warning appears
- Choose "Sort anything that looks like number as number"
Still messy? I use helper column: =ISNUMBER(A1) to identify fakes.
Your Burning Date Format Questions Answered
Can Excel remember my preferred date format?
Unfortunately no. You must set formats per workbook. I create templates with pre-set formats to avoid starting from scratch.
Why does Excel keep changing my dates automatically?
Auto-correction "feature". Disable via:
- File → Options → Proofing → AutoCorrect Options
- Uncheck "Replace as you type"
But this also disables useful corrections. Trade-offs...
How to change date format in Excel for Mac?
Same methods apply. Shortcut is Command+1 instead of Ctrl+1. Regional issues are identical too.
Can I use formulas with custom formatted dates?
Formulas use serial numbers, not displayed text. MONTH() will return 3 whether cell shows "March" or "03".
Best format for sorting by month-year?
Create helper column: =TEXT(A1,"yyyymm") → outputs sortable values like "202303".
Format Code Cheat Sheet
| Code | Effect | Example Output |
|---|---|---|
| d | Day without leading zero | 5 |
| dd | Day with leading zero | 05 |
| ddd | Abbreviated weekday | Wed |
| dddd | Full weekday | Wednesday |
| m | Month without zero | 3 |
| mm | Month with zero | 03 |
| mmm | Abbreviated month | Mar |
| mmmm | Full month | March |
| yy | Two-digit year | 23 |
| yyyy | Four-digit year | 2023 |
Mix codes freely: "dd-mmm-yy" gives "15-Mar-23". Personally love "mmm yy" for dashboards – saves space.
Final Reality Check
After years of Excel date wars, here's my practical hierarchy:
- For quick display changes: Format Cells dialog (Ctrl+1)
- For permanent text conversion: TEXT function
- For broken text dates: Text-to-Columns
- For recurring automation: Power Query or VBA
No method is perfect. TEXT makes un-calculatable text. Format Cells doesn't persist in exports. Text-to-Columns requires manual steps. Pick your poison based on the task.
The key is diagnosing whether you're fighting display formats or fundamental data issues. Once you know that, changing date formats in Excel becomes less rage-inducing. Mostly.
Still stuck? Happens to everyone. Search for "how to change date format in Excel" with your exact symptom – chances are someone survived that specific hell before you.
Comment