Fix Date Formats
Is 01/02 March 2nd or January 2nd? Excel's date chaos — American versus European orders, text that looks like dates but is not, numbers where dates should be — corrupts spreadsheets silently. The fix is almost always formatting, not retyping. See the numbers-instead-of-dates cousin problem and the lookup wizard for cleaner data.
TL;DR / Quick Fix
Quick fix: when Excel scrambles dates, the cells are usually formatted as text — select them, change the format to Date, and pick your region's order. For stubborn cases, Text to Columns forces a proper conversion. Fix the format once and the chaos stops spreading.
Follow these steps
- Diagnose text versus date. If dates left-align like text, Excel sees text, not dates. Real dates right-align and respond to format changes — check first.
- Apply the Date format. Select the cells, open Format Cells, choose Date, and pick your region's order (month-day or day-month). One change fixes the display.
- Force conversion with Text to Columns. For stubborn text-dates: select the column, use Data, Text to Columns, and step through choosing the date order. This rebuilds real dates from text.
- Set the region deliberately. Match the format to how your audience reads dates. Mixed regions in one sheet guarantee confusion — pick one and be consistent.
- Sort to verify. Sort the column oldest to newest. Real dates sort chronologically; text-dates sort alphabetically and expose themselves immediately.
- Prevent future chaos. Format date columns as Date before entering data, and consider data validation to reject non-date entries at the door.
What NOT to do
- Do not retype dates by hand. Reformatting converts in bulk; retyping wastes hours and introduces typos.
- Do not mix date orders in one column. Half American, half European is unfixable ambiguity — standardize first.
- Do not ignore the green triangles. Those error flags often mark text-dates; the fix-it menu converts them directly.
- Do not trust dates in imported files. CSVs and exports arrive as text constantly — convert before calculating.
Frequently asked questions
Why do my dates show as numbers?
Excel stores dates as serial numbers underneath. You are seeing the raw value — applying the Date format reveals the date.
How do I stop Excel changing my dates?
Pre-format the column as Text before pasting if you truly want text, or as Date if you want dates. Formatting first prevents auto-guessing.
Which date order should I use?
Match your region and audience. The ISO year-month-day order avoids all ambiguity in international files.