Check dates in a spreadsheet before sorting
Distinguish stored date values from text and resolve day-month ambiguity before analysis.
Follow the method ↓
The answer in 30 seconds
A cell can display a familiar date while containing plain text or a date serial value. An ambiguous entry such as 03/04/2025 can be interpreted differently by locale, and formatting alone may only change its appearance.
Inspect the source format and locale, test several unambiguous sample dates, convert a copy if needed and confirm chronological sorting before relying on filters or calculations.
Examples to adapt
Specify column type and locale before the spreadsheet guesses dates.
Find text entries that sort apart from recognised dates.
Confirm that source dates and reporting periods use the same definition.
Follow the method
- 1Preserve the source
Keep the original file and record its documented date format.
- 2Inspect examples
Check dates with a day above 12 and examine the cell's underlying value or type.
- 3Convert a copy
Use an explicit date conversion or import setting for the known source format.
- 4Validate sorting
Sort a sample chronologically and compare early, late and ambiguous entries.
A checklist to keep
Use these checks to record what you found. The grid supports a decision; it does not make one for you.
| Check | What to examine | Action |
|---|---|---|
| Source | What date notation is documented? | Find |
| Type | Text or recognised date? | Inspect |
| Locale | Which order was assumed? | Check |
| Sample | Do unambiguous dates agree? | Test |
| Sort | Does the order make sense? | Verify |
Free, no sign-up. UTF-8 text with semicolon-separated columns.
What to check
Changing cell display format does not guarantee correct interpretation.
Two-digit years and locale settings add ambiguity.
A wrong import can silently change dates used in calculations.
Common questions
Can a displayed date be text?
Yes. Test the cell type and how it behaves in date calculations or sorting.
What if 03/04 is ambiguous?
Find the source specification or ask its publisher; do not guess from that cell alone.
Sources and documentation
Documentation consulted on . Examples are illustrative; interfaces and results may change.