The Spreadsheet Had 14 Date Formats. Here's How I Tamed Them

Last spring a logistics client sent me a workbook with 48,000 shipment rows and a single column called Date. It looked innocent. It was not. By the time I finished profiling it, I had counted fourteen distinct formats, three languages for month names, and a few hundred cells where someone had typed TBD in capitals.

This post is the cleanup I wish I had planned from the start.

Step one: count before you fix

The first instinct is to throw pd.to_datetime at the column and hope. Resist it. Silent coercion will happily turn 03/04/2023 into March 4 when the warehouse in Gdańsk meant 3 April. Instead, I profile the shapes of the strings by replacing every digit with a placeholder.

import pandas as pd
import re

df = pd.read_excel('shipments.xlsx', dtype=str)
shapes = df['Date'].fillna('').map(lambda s: re.sub('[0-9]', 'd', s.strip()))
print(shapes.value_counts().head(20))

That one output told me most of the story: dd/dd/dddd dominated, but there were also dddd-dd-dd rows from an API export, Excel serial numbers like ddddd, and free text like 12 Mar 23.

Step two: parse each shape on purpose

Once you know the shapes, parse each group with an explicit format and keep a record of which rule matched. I keep a small list of patterns paired with formats and apply them in order, writing results into a new column rather than overwriting the original.

• ISO strings from the API parse cleanly with format='%Y-%m-%d'.
• Excel serials convert with an origin of 1899-12-30, a detail that has burned me more than once.
• The slash dates needed a conversation, not code.

Step three: ask a human about ambiguity

Of the 31,000 slash-formatted rows, about 9,400 were ambiguous: both numbers were 12 or below. I grouped them by the Origin_Warehouse column and found that each site was internally consistent. The UK and Poland sites wrote day first, the Ohio site wrote month first. A ten-minute call with the operations lead confirmed it. No algorithm would have been as reliable as that call.

The rules I follow now

1. Always read raw dates as strings, never let the reader guess.
2. Keep the original column untouched and add a parsed column beside it.
3. Add a third column naming the rule that parsed each row, so errors are traceable.
4. Treat anything unparsed as a finding to report, not a nuisance to drop.

In the end, 212 rows stayed unparsed. I sent them back to the client with a short note instead of quietly deleting them. Two of those rows turned out to be the delayed shipments they had been arguing about for a month.

Messy data is rarely random. It is usually a map of how different people work.

That is the part I enjoy most. Fixing dates is plumbing, but the plumbing tells you who built the house.

Cover: Photo by Gorilla ROI Data Connector on Unsplash.