How-to
How to Convert Dates to the Exact Format an Import Requires
An import rejects your dates or reads them wrongly? Convert the column to the exact pattern it asks for, such as DD/MM/YYYY, with no time-zone label added.
The problem
“The import keeps failing. The other system only accepts dates as DD/MM/YYYY, and my file has dates like June 29, 2024.”
Why imports are so strict
A program that imports a file, such as a CRM, an email platform, an accounting package or a database loader, reads dates with one fixed pattern. It does not guess the way a person does. A date that does not match the pattern character for character is rejected, or worse, read differently from what you meant:
June 29, 2024fails aDD/MM/YYYYimport: it has words where the pattern wants digits.6/9/2024may fail a pattern that requires two-digit days and months (06/09/2024).2024-06-29 13:45:07 UTCfails a date-only field because of the time and the zone label.06/09/2024passes aMM/DD/YYYYimport when you meantDD/MM/YYYY, and becomes June 9 instead of 6 September, with no error at all.
Spreadsheets add a second problem: Excel shows dates in your regional format and saves CSV files the same way, so the pattern you set in a cell may not be what ends up in the file.
Fix it on Date And Time Converter
- Find the exact pattern the import expects in its documentation or its error message, and translate it into tokens:
DD/MM/YYYY,YYYY-MM-DD,MM/DD/YYYY HH:mm, and so on. - Check one value in the converter: paste a date from your file, pick that pattern as the target, and compare the result with what the import wants. The converter writes exactly the chosen pattern; it adds a time-zone label only if you switch on Add time-zone label, unless the format itself writes an offset (ISO 8601 with
Zor±hh:mm, RFC 2822). - Convert the column in the file workspace with the same target. Make sure the source format is the one your file really uses; if the column could be day-first or month-first, settle that first (see swapped day and month).
- Give the converted file to the import directly. Opening it in Excel and saving it again can change the dates back to your regional format.
- Input
June 29, 2024read as MMMM D, YYYY (detected)- Result
29/06/2024DD/MM/YYYY · DD/MM/YYYY · clock kept as written · no timezone conversion
A whole column?
Open the CSV in the file workspace: pick the column, the formats and the zones, and the first 8 rows are converted at once, with counts for the whole file. The file is read in your browser and never uploaded. Downloading the converted file is part of Pro ($9.99/month).
Reading a date pattern
| Token | Means | 29 June 2024, 1:45:07 PM |
|---|---|---|
| YYYY / YY | Four-digit / two-digit year | 2024 / 24 |
| MM / M | Month, zero-padded / not | 06 / 6 |
| MMM / MMMM | Month name, short / full | Jun / June |
| DD / D | Day, zero-padded / not | 29 / 29 |
| HH / h | Hour, 24-hour padded / 12-hour | 13 / 1 |
| mm, ss | Minutes, seconds | 45, 07 |
| A | AM or PM | PM |
Some systems spell patterns differently: Excel writes dd/mm/yyyy (lower case, and mm means minutes when it follows hours or comes just before seconds), Python writes %d/%m/%Y, Java and .NET use dd/MM/yyyy. The date they describe is the same.
In Excel or code
In Excel or Google Sheets, build the text the import wants in a new column, then copy it and paste as values:
=TEXT(A2,"dd/mm/yyyy") This works only if A2 is a real date, not text that looks like one. Format codes follow the language of Excel’s installation in some versions (a German Excel writes TT.MM.JJJJ), so a formula copied from elsewhere may need its codes translated.
In code, parse with the source pattern and format with the target one:
# pandas
pd.to_datetime(df["date"], format="%B %d, %Y").dt.strftime("%d/%m/%Y") Pitfalls
- The import’s own order setting. Many systems read slashed dates according to an account or regional setting, not only the file. If the import swaps day and month even though the pattern is right, check that setting.
- Times where only a date fits. A date-only field may reject a time, or silently cut it off. Convert to a date-only pattern if the field holds dates.
- Zones in the value. Some imports reject
Z, an offset or a label. Convert to the zone the system expects first, then write the pattern without a zone.
Questions
What is the difference between DD and D, or MM and MMM?
DD is a two-digit day (07) and D drops the zero (7). MM is a two-digit month (06), M drops the zero, MMM is a short name (Jun) and MMMM the full name (June). YYYY is four digits and YY two.
Should the dates carry a time zone?
Only if the import asks for one. The converter writes exactly the chosen format: a pattern such as DD/MM/YYYY gets a zone label only when you turn on “Add time-zone label”, while a format that carries its own offset (ISO 8601 with Z or ±hh:mm, RFC 2822) always writes it.
My dates are in the right format but the import still swaps day and month. Why?
The importer has its own idea of the order, often taken from an account or regional setting. Check that setting, or use a format with a written month or a four-digit year first if the importer accepts one.