> AI agents: this is one page from Mammoth Analytics documentation. The index of all pages as Markdown is https://mammoth.io/docs/llms.txt. Append `.md` to any docs URL, or send `Accept: text/markdown`, to get Markdown.

# Convert text to a date with format codes in Mammoth

> Turn text into real dates with Convert Column Type: Source - Auto, Day First or Month First, PostgreSQL-style format codes, ISO and mixed formats.
>
> Applies to: Where: View › Describe your transformation › Column Management › Convert Column Type › Date › Define Source Format

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:

```plaintext
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.


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

## Related

- [Convert text to dates and numbers](https://mammoth.io/docs/learn/clean-and-shape/convert-column-type/): the Convert Column Type steps and settings.
- [Convert text to numbers and Unix timestamps to dates](https://mammoth.io/docs/learn/clean-and-shape/convert-numbers-and-timestamps/): when the date is a number.
- [Work with dates and financial years](https://mammoth.io/docs/learn/clean-and-shape/dates/): calculate with dates once they are real dates.

---
Source: https://mammoth.io/docs/learn/clean-and-shape/convert-text-to-date.md · Updated: 2026-11-08