Data and spreadsheets
Find ambiguous dates in a spreadsheet
A value like 04/05/2024 can mean April 5 or May 4. List every date with two valid readings, note where the file came from, and confirm the intended order with a person or the source system. Convert only after that confirmation.
A text to start with
This column holds dates exported from [source system or person]: [anonymous sample values]. The file may have come from [country or region, if known]. Identify which values could be read as either day-month or month-day, and which have only one valid reading. Do not convert or guess. For each ambiguous value, list what I need to confirm.
Example request. Change the details to fit your situation.Ambiguous date log
File and column: [ ] Source system or author: [ ] Source locale or country, if known: [ ] Row reference: [ ] Original value as shown: [ ] Meaning if day-month: [ ] Meaning if month-day: [ ] Only one valid reading? [ ] Confirmation needed from: [ ] Confirmed meaning: [ ] Date of confirmation: [ ] Rows left unresolved: [ ] Repeat the item block as needed.
Why a guess is risky
A date such as 13/04/2024 can only be day-month, so it is tempting to assume the rest of the column follows the same order. A file can mix orders if several people entered it or two systems fed it. Check whether some values are text rather than true dates, since software may have reinterpreted others on import. Look for two-digit years and mixed separators. If both readings are valid and nothing in the file settles it, leave the value unconverted and flagged.
Ask another agent to check the result
Check this list of dates. Where both day-month and month-day are valid, do not pick one. Mark the row as needing confirmation. Point out any row I treated as unambiguous that actually has two readings, and any value stored as text.
Confirm, then convert
Send the list of ambiguous values to whoever created the file, or check the source system's date order. Record the answer for each batch of rows. On a copy, convert confirmed values to an unambiguous form such as year-month-day. Keep the original column and leave unresolved rows flagged.