Impossible dates
A value is shaped like a date but names a day that does not exist or could not have happened.
What it looks like
Before
| start_date | end_date | check |
|---|---|---|
| 2024-02-30 | 2024-03-15 | · |
| 2023-02-29 | 2023-03-10 | · |
| 2024-05-01 | 2024-04-01 | · |
| 2024-06-03 | 2024-06-20 | · |
After
| start_date | end_date | check |
|---|---|---|
| 2024-02-30 | 2024-03-15 | start date does not exist |
| 2023-02-29 | 2023-03-10 | start date does not exist, 2023 is not a leap year |
| 2024-05-01 | 2024-04-01 | end is before start |
| 2024-06-03 | 2024-06-20 | valid |
How it happens
- The date was typed into a free text field that checks nothing.
- Day and month were swapped, or a digit was mistyped, producing a month above twelve or a day past the end of the month.
- Each date was valid when entered, but two related dates were filled in separately and nobody compared them.
What it breaks
- Strict software rejects the row. Lenient software quietly rolls the date forward into the next month or loads it as empty.
- Durations come out negative, and ages or tenures become absurd.
- Records with a future date drop out of reports that look at the period up to today.
How to spot it
- Parse the column strictly and list every value that fails.
- Check ranges against what the column means: birth dates in the future, events before the organization existed.
- Compare related dates on the same row. An end before a start, or a shipment before an order, is a sign.
How to fix it
- Separate dates that cannot exist from dates that exist but are implausible. They have different causes.
- Correct only where the intent is clear from other evidence, such as a matching date elsewhere on the row.
- Leave the rest flagged for a person. The true date is not in the cell, and any automatic repair invents one.
- Ambiguous day and month orderA date such as 03/04/2025 could mean March 4 or April 3, and nothing in the cell says which.
- Two-digit yearsA year written as two digits does not say which century it belongs to.
- Outliers from entry errorsA value sits far outside the rest because of a slipped key, an extra zero, or a misplaced decimal point.
TermsValidation, Validity, Business rule, Human review