Ambiguous day and month order

• Different countries write the day and the month in different orders, and data crosses borders.
• A file was opened on a computer with different regional settings, which reinterpreted the dates it could and left the others alone.
• Two-digit days of twelve or less are valid either way, so nothing flags them at entry.

• No error is raised. The date is valid; it is just the wrong date.
• Monthly totals shift between months, and the shift is hard to see because only some rows are affected.
• Deadlines, ages, and durations computed from the column are wrong by weeks or months.

• Look for any value where the first number is greater than twelve, and any where the second is. If both exist, the column mixes orders.
• Check whether the dates make sense in sequence. A run of consecutive days that suddenly jumps a month is a sign.
• Compare against another column that should agree, such as a created timestamp or a fiscal period.

• Find out where each batch of rows came from. Source is the most reliable evidence for order.
• Use the unambiguous rows from the same source to decide the order for the ambiguous ones.
• Where the evidence does not settle it, ask a person. A wrong guess here is invisible afterward.
• Write the result year first so the question cannot come up again.

• Mixed date formatsOne column holds dates written in several different styles.
• Case example: Non-profitsA donor list that counted the same people twice
• Case example: Finance teamsA vendor list that paid the same invoice twice

TermsISO 8601, Parsing, Validation