Fixing dates that Excel mangled

Dates are the most common thing that goes wrong when a spreadsheet moves between people, apps and countries. Almost every broken date falls into one of four patterns, and each has a safe fix.

1. Day/month or month/day?

03/04/2024 is 3 April in the UK and most of Europe and March 4 in the US. Nothing in the text says which. A tool that silently picks one will be wrong for half the world, and the error is invisible: every value still looks like a valid date.

The only reliable evidence is inside the column itself:

• If any value has a first number above 12 (25/04/2024), the column is day-first.
• If any value has a second number above 12 (04/25/2024), it is month-first.
• If both happen in one column, two sources were pasted together and each part needs its own fix.
• If neither happens, the data cannot tell you. Ask whoever exported it, or check a row you know (an order you remember, a public holiday).

GridRinse follows exactly these rules. When a column is undecidable it leaves it untouched and asks you to choose DMY or MDYfor that column, instead of guessing.

2. Dates that turned into numbers

Excel stores a date as the number of days since 30 December 1899, so 45296 is 5 January 2024. If a date column loses its formatting (copying values, some CSV exports, a database import) you see the raw number. Excel also keeps a famous bug from Lotus 1-2-3: it believes 1900 was a leap year, so serials before 1 March 1900 are off by one.

To fix them, choose the column explicitly in Standardise dates and tick Treat plain numbers as Excel date serials. It is off by default because a column of quantities or IDs would otherwise turn into dates. A fractional part is the time of day: 45296.5 is noon.

3. The same date written five ways

Data typed by hand or merged from several systems mixes 2024-01-05, Jan 5, 2024, 5 Jan 2024 and 1/5/2024. Unambiguous forms (ISO and those with a month name) can be read safely in any column. Pick one output format for everything.YYYY-MM-DD is the best choice for data you will process further: it sorts correctly as text and every tool reads it.

4. Excel re-formats dates when it opens your CSV

Opening a CSV by double-clicking lets Excel re-interpret dates using your computer’s regional settings, and saving writes them back in that new form. To avoid it, import the CSV with Data → From Text/CSV and set the column type to Text, or download the cleaned file as XLSX instead of CSV.

Checklist

1. Load the file and look at each date column in the Original tab.
2. Let the date step detect columns; answer DMY or MDY for any flagged column.
3. Check the warning count: values that could not be read are listed and left unchanged.
4. Download as XLSX if the file will be opened in Excel.

Open the cleaner