Synthetic date column: which rows a rule can settle, and which must stay on the ambiguity list

The invented rules

All entries, offices and rules here are invented. The Leeds office types day first. The Austin office types month first. The system export writes year-month-day. Two-digit years follow Microsoft's documented reading: 00 to 29 mean 2000 to 2029 and 30 to 99 mean 1930 to 1999. Output is year-month-day.

Nine entries and what is done with each

Each row shows the source, the typed text, the decision and the reason. Only rows with a stated source or a logical impossibility are converted.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

row | source  | typed text   | decision                | result      | reason
1   | Leeds   | 03/04/2026   | converted by rule       | 2026-04-03  | Leeds is day-first
2   | Austin  | 03/04/2026   | converted by rule       | 2026-03-04  | Austin is month-first
3   | system  | 2026-04-03   | converted               | 2026-04-03  | already year-month-day
4   | unknown | 05/06/2026   | ambiguity list          | (blank)     | 2026-06-05 or 2026-05-06; no source
5   | Leeds   | 27/04/2026   | converted by rule       | 2026-04-27  | day-first, valid
6   | unknown | 27/04/2026   | converted, logged       | 2026-04-27  | 27 cannot be a month, so only day-first is possible
7   | Austin  | 31/12/2025   | exception               | (blank)     | month-first rule makes month 31, which is impossible; typo or wrong source
8   | Leeds   | 5 Mar 26     | converted, logged       | 2026-03-05  | two-digit year 26 is read as 2026 by the agreed cutoff
9   | system  | 2030-04-04   | exception               | (blank)     | paper record says 2026-04-03; difference is exactly 1,462 days

What the last row shows

Microsoft documents two date systems, 1900 and 1904, that differ by 1,462 days, which is four years and one day; its example is July 5, 2011, which is 40729 in one and 39267 in the other. A date that is exactly 1,462 days away from a known source date suggests a workbook that used the other system. Row 9 is an authored illustration of that pattern.

• A whole column offset by 1,462 days suggests a date-system mismatch, which can be corrected for the whole column once confirmed.
• A single row offset by that amount is listed for the owner to check against the source.

The result, as counts

The conversion is reported as counts, with every uncertain row visible.

• Converted by a stated source: rows 1, 2, 3 and 5 (four).
• Converted by logic or a cutoff and logged: rows 6 and 8 (two).
• On the ambiguity list: row 4 (one).
• Exceptions for the owner: rows 7 and 9 (two).
• Four + two + one + two = nine entries, with none left unaccounted for.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

category                          | rows           | count
converted by stated source        | 1, 2, 3, 5     | 4
converted by logic or cutoff      | 6, 8           | 2
ambiguity list                    | 4              | 1
exceptions for the owner          | 7, 9           | 2
all                               |                | 9

Use it to specify an enquiry

For one file of up to 20,000 rows with up to three date columns, the clean-up job is from £125 (an untested proposal, with the final price confirmed after we see the column list), accepted by checks like these and a reference set of at least thirty dates you confirm, with payment after your sign-off. It never guesses a date. Send the column list and what you know about each source, not the file.