Skip to content

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, 2024 fails a DD/MM/YYYY import: it has words where the pattern wants digits.
  • 6/9/2024 may fail a pattern that requires two-digit days and months (06/09/2024).
  • 2024-06-29 13:45:07 UTC fails a date-only field because of the time and the zone label.
  • 06/09/2024 passes a MM/DD/YYYY import when you meant DD/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

  1. 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.
  2. 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 Z or ±hh:mm, RFC 2822).
  3. 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).
  4. 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.
Worked example: A written date turned into the DD/MM/YYYY pattern, zero-padded.
Input
June 29, 2024 read as MMMM D, YYYY (detected)
Result
29/06/2024 DD/MM/YYYY · DD/MM/YYYY · clock kept as written · no timezone conversion

Open in the converter: June 29, 2024 → 29/06/2024

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).

Open the file workspace

Reading a date pattern

TokenMeans29 June 2024, 1:45:07 PM
YYYY / YYFour-digit / two-digit year2024 / 24
MM / MMonth, zero-padded / not06 / 6
MMM / MMMMMonth name, short / fullJun / June
DD / DDay, zero-padded / not29 / 29
HH / hHour, 24-hour padded / 12-hour13 / 1
mm, ssMinutes, seconds45, 07
AAM or PMPM

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.