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 closer look
Resolve ambiguity before sorting
Fictional example: two teams supply a file, one using day/month/year and the other month/day/year. The string 04/05/2026 alone does not identify the day.
| Received value | Possible interpretation | Decision |
|---|---|---|
| 04/05/2026 | 4 May or 5 April | Keep as text until the convention is known |
| 13/05/2026 | Consistent with day/month/year | A useful clue, but confirm the convention for the whole file |
| 31/02/2026 | Impossible in the expected calendar | Flag the error without guessing a correction |
Keep the original value in a separate column. After conversion, inspect minimum, maximum and sample dates around month boundaries.
Can an impossible date be replaced with the last day of the month?
Only if a documented producer rule requires it. Otherwise you invent a date. Flag it and seek a correction, or explain why it is excluded from the relevant calculation.
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 |
Read the checklist directly in the table.
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.