How to Standardise Date Formats in a CSV File

You open a CSV and the date column looks fine. Then you load it somewhere else and half the dates are wrong, or January 3rd silently became March 1st.

Date format problems in CSV files are easy to miss because the data looks correct until it’s read by a different tool. Standardising date formats before you use the file is what prevents silent data corruption downstream.

Why date formats break in CSV files

CSV files have no schema. There’s no metadata telling the reader whether 04/05/2024 means April 5th or May 4th. Every tool reading that file makes its own assumption, and tools disagree.

The three dominant date format families are:

• MM/DD/YYYY: US default, used by most American software
• DD/MM/YYYY: European default, used by most of the rest of the world
• YYYY-MM-DD: ISO 8601, used by databases, APIs, and most technical tooling

If your file was exported by software using one convention and your destination reads it with another, any date where the day value is 12 or below becomes ambiguous. 04/05/2024 is either April 5th or May 4th depending on which tool reads it. Neither tool will warn you. Both will silently accept the wrong interpretation.

How Excel makes this worse

Excel is the most common way people open and edit CSV files, and it actively makes date format problems harder to solve.

When you open a CSV in Excel, it reads date-looking strings and converts them to Excel’s internal date serial format based on your system locale. 05/04/2024 in a British locale becomes May 4th. In a US locale, it becomes April 5th. Excel doesn’t tell you it made this decision. The cell just shows a date.

When you then save the file as CSV, Excel writes the date back out in your locale’s default format. If the original file had 2024-04-05 (ISO format), and you open and re-save it in Excel with a US locale, it may come out as 4/5/2024. The ISO format is gone.

Three specific ways Excel corrupts dates:

• Auto-converting ISO strings: 2024-04-05 gets converted to a date serial and re-exported as 4/5/2024
• Losing leading zeros: 04/05/2024 becomes 4/5/2024 after save
• Two-digit years: 04/05/24 might become 04/05/1924 or 04/05/2024 depending on Excel’s cutoff year setting

The safest rule: if you need to inspect a CSV with dates, use a text editor or Google Sheets (which preserves the original string without auto-converting it).

How to detect which date format your file uses

The actual format in the file needs identifying before any conversion happens.

Opening the file in a text editor instead of Excel shows the raw date strings before anything auto-converts them. From there, the raw date column answers four questions:

1. What’s the separator? Slash (/), dash (-), or dot (.)?
2. What order are the parts in? Is the four-digit year first, middle, or last?
3. Are there any dates where day > 12? If you see 25/04/2024, the first number must be the day: it’s DD/MM/YYYY. If every value in the column has a first number ≤ 12, the format is ambiguous without more context.
4. What’s the source? A US-based SaaS export is almost always MM/DD/YYYY. A European tool is almost always DD/MM/YYYY. Database or API exports are almost always YYYY-MM-DD.

Common patterns to recognise:

Format	Example	Typical source
MM/DD/YYYY	04/25/2024	US tools, most American SaaS
DD/MM/YYYY	25/04/2024	European tools, UK locale
YYYY-MM-DD	2024-04-25	Databases, APIs, ISO 8601
MM/DD/YY	04/25/24	Legacy exports
DD.MM.YYYY	25.04.2024	German-locale tools
MMMM D, YYYY	April 25, 2024	Activity exports, email tools
D MMM YYYY	25 Apr 2024	Some UK software exports

If multiple formats are mixed in the same column, the data was probably merged from multiple sources, each with its own locale setting, and each format needs handling separately before combining.

Converting date formats in Google Sheets

Google Sheets is safer than Excel for this because it doesn’t silently reformat dates on open. Importing the CSV via File → Import → Upload, with “No conversion” selected, keeps the raw strings intact.

To convert a date column to ISO 8601 (YYYY-MM-DD), the safest target format because it’s unambiguous:

If your source is MM/DD/YYYY:

=TEXT(DATEVALUE(A2), "YYYY-MM-DD")

If your source is DD/MM/YYYY (Google Sheets may not auto-parse this, so parse manually):

=TEXT(DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)), "YYYY-MM-DD")

If your source is already YYYY-MM-DD but has been corrupted by Excel into a serial number:

=TEXT(A2, "YYYY-MM-DD")

After applying the formula column, pasting special as values only strips the formula, and the original column can be deleted before the file is exported as CSV.

Converting date formats with find and replace (regex)

For consistent source formats, a text editor with regex support (VS Code, Notepad++) can reformat an entire column in one pass without opening the file in any spreadsheet tool.

In VS Code, Ctrl+H opens find-and-replace, with a toggle to enable regex mode.

To convert YYYY-MM-DD to DD/MM/YYYY:

• Find: (\d{4})-(\d{2})-(\d{2})
• Replace: $3/$2/$1

To convert MM/DD/YYYY to YYYY-MM-DD:

• Find: (\d{2})/(\d{2})/(\d{4})
• Replace: $3-$1-$2

This only works reliably when the format is consistent across the entire column. Mixed formats need to be handled case by case.

Choosing a target format

ISO 8601 (YYYY-MM-DD) is the safest target for any CSV that will be read programmatically or imported into a database or API. It sorts correctly as a string (alphabetical order = chronological order), it’s completely unambiguous, and it’s what most data tools expect.

If your destination requires a specific format, check its documentation first. When in doubt, ISO 8601 is the right default.

Checklist before exporting your cleaned file

• Open the original CSV in a text editor to see the raw date strings before any tool touches them
• Identify the source format: look for dates where day > 12 to remove ambiguity
• Choose a target format (ISO 8601 is the safe default)
• Convert in Google Sheets or with a regex tool, not Excel
• Check a sample of rows after conversion, especially dates near month boundaries (e.g. the 12th)
• If your file has multiple date columns, handle each separately: they may have come from different sources with different locale settings
• Confirm the column header accurately describes what’s in the column

If your file has other normalisation needs alongside date conversion (column renaming, picklist standardisation, value cleanup), Asphorem’s CSV Normalizer handles these in a single pass, so you don’t need to run separate cleanup steps before the file is ready to use.

CSV Date Formats: Frequently Asked Questions

How do you convert MM/DD/YYYY to YYYY-MM-DD in a CSV?+

In Google Sheets: =TEXT(DATEVALUE(A2), "YYYY-MM-DD"). In a regex-capable text editor: find (\d{2})/(\d{2})/(\d{4}) and replace with $3-$1-$2. For DD/MM/YYYY sources, the regex replacement is $3-$2-$1. Checking a sample after conversion confirms the day/month order came out right.

Why does Excel keep changing my date format?+

Excel auto-converts any date-looking string to its internal date serial format on open, then writes it back in your system locale’s format on save. An ISO 2024-04-05 opened and re-saved in a US-locale Excel becomes 4/5/2024. Google Sheets or a text editor avoids this for any file that contains dates.

What’s the safest date format for cross-system imports?+

ISO 8601 (YYYY-MM-DD). It’s unambiguous (no day/month order confusion), it sorts correctly as a string, and most modern tools (databases, APIs, HubSpot, Pipedrive) accept it directly.

How do you detect which date format a CSV is using?+

Any date where the first number is greater than 12 proves the format is DD/MM/YYYY, since the first part must be the day. If every date in the column has both numbers at 12 or below, the format is ambiguous without context, and the source becomes the next signal: US tools usually export MM/DD/YYYY, European tools export DD/MM/YYYY, and databases export ISO 8601.

How do you convert dates in a European CSV when the format is DD.MM.YYYY?+

In Google Sheets: =TEXT(DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)), "YYYY-MM-DD"). The function reconstructs the date from positions in the string regardless of separator. The full set of European format issues is covered in why European CSV files break in CRM imports.