Learn › Clean and shape data › Convert column types

Convert text to a date with format codes

To turn a text column into a date, use Convert Column Type, choose Date, and click Define Source Format (the gear icon) to say what the text looks like. With Source - Auto, Mammoth samples the column's values and detects the formats present, which works for most real-world data. For ambiguous, uncommon or mixed formats, choose a source format or give format codes. Values that don't match become empty (NULL).

Tell Mammoth the order of day and month

A date made only of numbers, such as 01/02/2025, could be 2 January (US) or 1 February (UK). Choose the source format to settle it:

Option	Reads 01/02/2025 as	Use when
Source - Auto	Detected from your data; may guess wrong	Your dates are mostly unambiguous (month names or clear patterns)
Source - DD/MM/YYYY (Day First)	1 February 2025	Dates are always day first
Source - MM/DD/YYYY (Month First)	2 January 2025	Dates are always month first
Source - Custom (multiple formats)	Your format codes	Mixed formats or non-standard patterns

If your dates are ambiguous, always choose Day First or Month First rather than Auto, to prevent silent parsing errors.

Date format codes for text dates

Mammoth reads text using PostgreSQL-style format codes:

Code	Meaning	Example	Notes
YYYY	4-digit year	2025	Use YY for 2-digit years (25)
MM	Month (01-12)	01
DD	Day of month (01-31)	15
HH24	Hour (24-hour, 00-23)	14	Use HH12 or HH for 12-hour format (01-12)
MI	Minute (00-59)	30
SS	Second (00-59)	45
MON / MONTH	Month name	Jan / January	MON is abbreviated, MONTH is full. Write the code in upper, title or lower case: MON, Mon, mon, MONTH, Month, month
DAY / DY	Weekday name	Wednesday / Wed	DAY is the full name, DY a 3-letter abbreviation. Upper, title or lower case
AM / PM	AM/PM indicator	AM	Write it AM, am, PM, pm, A.M., a.m., P.M. or p.m.
TZ	Time zone name	UTC	A time zone abbreviation, such as PST, EST or UTC
OF	Time zone offset	+00:00	±HH:MM or ±HHMM
Literal "T"	The letter T	T	Matches the T in ISO format (2025-01-15T14:30:00)
Literal "Z"	The letter Z	Z	Matches the ISO Zulu indicator (2025-01-15T14:30:00Z)

Common date formats and their codes

Format	Format code	Input	Result
ISO	`YYYY-MM-DD`	2025-01-15	January 15, 2025
ISO 8601 with time	`YYYY-MM-DD"T"HH24:MI:SS"Z"`	2025-01-15T14:30:45Z	January 15, 2025 2:30:45 PM (UTC)
US (month/day/year)	`MM/DD/YYYY`	01/15/2025	January 15, 2025
UK (day/month/year)	`DD/MM/YYYY`	15/01/2025	January 15, 2025
Long, with month name	`DD MONTH YYYY`	15 January 2025	January 15, 2025
European (day.month.year)	`DD.MM.YYYY`	15.01.2025	January 15, 2025

Convert a column that mixes date formats

Choose Source - Custom (multiple formats) and enter several format codes separated by semicolons:

Format: YYYY-MM-DD; MM/DD/YYYY; DD.MM.YYYY

Mammoth tries each format in order until one works. This helps with data imported from several sources, history where the format changed over time, and inconsistent data entry.

Troubleshooting text to date conversions

Problem	Cause	Fix
All cells become empty	The format code doesn't match your data	Check the real date format in the source. Try Source - Auto first.
Some cells are empty	Some values don't match the format	Look for typos, extra spaces or inconsistent formats in those rows.
"01/02/2025" reads wrong	Ambiguous US or UK date	Choose Day First or Month First instead of Auto.
Extra text after the date ("2025-01-15 extra text")	The format covers only the date	That's fine: the format only needs to match the date part. Extra characters are ignored.
"2025-01-15 14:30:00" doesn't convert	The format has no time part	Use `YYYY-MM-DD HH24:MI:SS`.

If Auto fails on a copy of the column, the data itself may have typos, mixed formats or invalid dates.

Convert text to dates and numbers: the Convert Column Type steps and settings.
Convert text to numbers and Unix timestamps to dates: when the date is a number.
Work with dates and financial years: calculate with dates once they are real dates.

Was this page helpful?

Something wrong? support@mammoth.io