Convert text to dates and numbers

Convert one or more columns with Convert Column Type

1. Click Describe your transformation (or a + in the Pipeline panel), choose Column Management, then Convert Column Type.
2. In Select columns to convert, choose the source column. Each row handles one column.
3. In Select new column type, choose the target. Number offers Number, ID, Percentage and Currency; Text offers Text and ID; Date is a single option. The column's current type is greyed out.
4. Set the output format if the panel asks for one (see the table below).
5. To convert another column in the same task, click + Convert more columns. Click the trash icon on a row to remove it.
6. Click Apply.

If a value can't be converted to the target type (for example "John Doe" to Number or Date), it becomes empty (null).

For example, an order_date column imported as Text holds values like "2025/01/15". Select it, choose Date, set the source format to Source - Auto, and set Format Output to YYYY/MM/DD. The column is now a real Date you can use in date filters and calculations.

Convert a column to ID, Currency or Percentage

Picking another entry under the column's own type (for example Currency for a Number column) changes only what the column means. It doesn't cast the values, except with the divide-by-100 option above. The number format in the panel follows the target:

Converting a code such as 000417 to ID under Text keeps the leading zeros. Converting it to a Number drops them. For what each type means, see Column types, IDs, currency and percentages.

Convert text to a date

You can let Mammoth detect the date format or give it format codes.

With Source - Auto, Mammoth samples the column's values and detects the formats present. This works for most real-world data. For uncommon or mixed formats, use format codes.

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:

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

Date format codes

Mammoth reads text using PostgreSQL-style format codes:

Common date formats and their codes

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.

Convert text to a number

Mammoth pulls the number out of formatted text. It strips a currency symbol in front of the number, comma thousand separators and a percent sign.

Currency symbols Mammoth recognises

These 5 symbols are recognised and stripped when they come before the number:

Thousand separators and European number formats

Mammoth removes comma thousand separators before converting. Standard grouping (1,234,567) and Indian-style grouping (12,34,567) both work. 1,234,567.89 and 1234567.89 both become 1234567.89.

European format isn't supported. Numbers that use a period as the thousand separator and a comma as the decimal (for example 1.234.567,89 or €1.234,56) aren't recognised and convert to empty. Use Find & Replace to change them to comma thousands and a period decimal first (1.234.567,89 → 1,234,567.89), then convert to Number.

Limits on converted numbers and timestamps

Convert a Unix timestamp to a date

When you convert a Number column to Date, Mammoth detects the time unit from the size of each value. You don't need to say which unit you have, because systems differ: JavaScript's Date.now() and Java's System.currentTimeMillis() return milliseconds (1735689600000), while Python's time.time() returns seconds (1735689600).

Two edge cases: 0 is read as seconds and gives 1970-01-01 00:00:00 (the Unix epoch start). -86400 gives 1969-12-31 00:00:00, one day before the epoch.

Check a conversion before you rely on it

1. Test your format code on a few rows before you convert the whole column.
2. Copy the column first with Copy Columns, so you can compare original and converted values.
3. Try Source - Auto first for standard date formats (ISO, US, UK). Use format codes only if Auto fails.
4. Don't use Auto for dates that could be day first or month first. Choose one.
5. After converting, spot-check the first rows, the last rows and any empty cells.
6. Remember text-to-number assumes a comma thousand separator and a period decimal point. Clean other conventions first.

Troubleshooting empty or wrong conversions

When a conversion fails, the cell becomes empty (NULL).

Text to date problems

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

Text to number problems

Number to date problems

Related

• Column types, IDs, currency and percentages: what each type means and where it shows.
• Rename, format and sort columns: change how a date or number looks without converting it.
• Work with dates and financial years: calculate with dates once they are real dates.
• Find and replace values: fix separators before converting.
• Add, split, copy and delete columns: copy a column before you convert it.