Skip to content
CSVTidy

How to Fix Mixed Date Formats in a CSV

3 min read

When one column contains 03/14/2024, 14 Mar 2024, and 2024-03-14 at once — how to detect the mess, resolve the 01/02 ambiguity, and convert everything to one format.

You open a CSV and the date column looks like this:

Signup Date
2024-03-14
03/15/2024
14 Mar 2024
April 2, 2024
5/1/24

Five rows, four formats. Sorting is meaningless, filtering by date range silently drops rows, and importing into any database throws parse errors.

Here's how to fix it — including the ambiguity trap that quietly corrupts data if you rush.

First: the 01/02/2024 problem

Before converting anything, understand this, because getting it wrong produces wrong data that looks fine.

01/02/2024 is genuinely ambiguous:

  • In the US it means January 2, 2024 (MM/DD/YYYY)
  • Almost everywhere else it means 1 February 2024 (DD/MM/YYYY)

No tool can tell these apart from the value alone. You have to know where the data came from.

The good news: most columns contain at least one unambiguous value. If you see 03/15/2024 anywhere in the column, there's no 15th month — so that column is MM/DD/YYYY, and every other value in it should be read the same way. Scan for any value where the first number exceeds 12; that settles it.

If every value in the column is ambiguous, go find out where the export came from. Guessing has a 50% chance of silently shifting every date in your file.

Excel: Text to Columns

Excel's most reliable date-fixing tool is oddly buried in the import features.

  1. Select the date column.
  2. Data tab → Text to Columns.
  3. Choose Delimited → Next → clear all delimiters → Next.
  4. Under Column data format, choose Date and pick the format that matches your source data (MDY for US, DMY for European).
  5. Finish.

Excel now parses the values as real dates. Format the display however you like via Format Cells → Date.

This works well when the whole column shares one format. With genuinely mixed formats in a single column, Excel converts what it recognizes and leaves the rest as text — you'll see them left-aligned instead of right-aligned, which is how you spot the failures.

Google Sheets: DATEVALUE

=DATEVALUE(A2)

Returns a serial number; format the result column as a date. DATEVALUE handles a decent range of formats but respects your spreadsheet's locale setting, so check File → Settings → Locale matches your data's origin before trusting it.

For values DATEVALUE rejects, you'll get #VALUE! — which is at least an honest failure you can see, rather than a silent misparse.

Python: pandas

import pandas as pd

df = pd.read_csv("signups.csv")
df["Signup Date"] = pd.to_datetime(
    df["Signup Date"],
    format="mixed",     # handles several formats in one column
    dayfirst=False,     # True for DD/MM data
)
df["Signup Date"] = df["Signup Date"].dt.strftime("%Y-%m-%d")
df.to_csv("signups-clean.csv", index=False)

Set dayfirst to match your source. To find rows that failed to parse rather than dropping them silently:

parsed = pd.to_datetime(df["Signup Date"], errors="coerce", format="mixed")
print(df[parsed.isna()])   # inspect these before proceeding

Which output format to pick

ISO 8601 (2024-03-14) — the right default. Sorts correctly as plain text, unambiguous worldwide, and accepted by every database and API. Use this unless you have a specific reason not to.

US (03/14/2024) — for files going to a US audience who'll open them in Excel.

EU (14/03/2024) — same, for European recipients.

Whatever you choose, apply it to the entire column. A column with two formats is worse than one with a format you dislike.

Watch out for Excel's autocorrect

If you open a CSV in Excel and immediately save it, Excel may have already rewritten your dates according to your machine's locale — before you did anything. It also famously converts values that merely look like dates into dates.

To avoid this, import rather than open: Data → From Text/CSV, and set the date column's type explicitly during import. Or do the cleanup before the file ever touches Excel.

Checklist

  • Determine whether the source is MM/DD or DD/MM — don't guess.
  • Look for a value with a first number above 12 to confirm.
  • Convert the whole column to one format, ideally ISO.
  • Check for rows that failed to parse instead of assuming they converted.
  • Verify the date range in the result looks plausible.

Keep reading