Formatting dates with DATETIME_FORMAT in Airtable

Note

• To parse a datetime from a string, use the DATETIME_PARSE() function. Learn more here.

• For more information on DATETIME_FORMAT and other formulas, check out the formula field reference article.

How DATETIME_FORMAT works in Airtable

There are times when a date field in an Airtable base needs to be formatted beyond what's available in the field's configuration. In those cases, referencing the date field in a formula field using DATETIME_FORMAT gives full control over the output format.

DATETIME_FORMAT reformats data from a date-type field into a string. It follows this structure:

DATETIME_FORMAT({yourDateTimeField}, 'format specifier(s)')

The format specifier controls how the date is returned—something like 'DD-MM-YYYY', 'YYYY/MM/DD', or 'MM.DD'. For the full list of supported specifiers, see the Supported DATETIME_FORMAT specifiers section below.

Here's a quick example of how DATETIME_FORMAT transforms a date field from one format to another.

{Location} & " ON " & DATETIME_FORMAT({Date}, "DD-MM-YYYY")

Notice how the date's format changes from MM/DD/YYYY in the Date field to DD-MM-YYYY in the Formula Examples field.

Reformatting a date for use in a primary field

Since a table's primary field should contain a unique value for each record, formula fields are often used to ensure this. The example below shows how to combine static text with a formatted date field to create unique record names.

Here's a table with this formula currently in the primary field: CONCATENATE("Experiment #", {Test ID})

Using the autonumber creates a unique name for each record, but a more dynamic approach ties the experiment name to the test date instead. The table has these fields:

1. "Experiment Name"—the primary field, which uses a formula, concatenating "Experiment #" with the Autonumber field (Test ID).

2. Test ID—an autonumber field tracking unique experiments.

3. Test Date—a date field tracking when each experiment took place.

To update Experiment Name to include the date, adding the date field directly to a formula without DATETIME_FORMAT outputs it in simplified ISO format—not easy to read. Here's what CONCATENATE("Experiment #", {Test Date}) produces:

Updating the formula with DATETIME_FORMAT and a specifier makes the output readable:

CONCATENATE("Experiment - ", DATETIME_FORMAT({Test Date},'M/DD/YYYY h:mm'))

This produces a more readable, unique experiment name for each record:

Supported DATETIME_FORMAT specifiers

Note

The specifiers "w" and "W" behave differently. "W" is the ISO week of year, while "w" is the locale week of year (Gregorian calendar in GMT). 2/11/21 falls on the 6th ISO week and on the 7th Gregorian week. Airtable uses Moment.js for date parsing.

DATETIME_FORMAT formatting examples

• ISO 8601—represents date and time in year-month-day-hour-minutes-seconds-milliseconds format.

  • For example, 2025-12-31 15:00:00.000 represents December 31, 2025 at 3:00pm. There's no timezone offset specified—the time is assumed to be in UTC.

    • DATETIME_FORMAT({Insert Date Field Name}, "YYYY-MM-DD HH:mm:ss.SSS")

• American datetimes—formats the date in month–day–year order:

  • December 31 2025

    • DATETIME_FORMAT({Insert Date Field Name}, "MMMM D YYYY")

  • 12/31/25

    • DATETIME_FORMAT({Insert Date Field Name}, "M/D/YY")

  • 12/31/2025

    • DATETIME_FORMAT({Insert Date Field Name}, "M/D/YYYY")

• UK datetimes—formats the date in day–month–year order:

  • 31 December 2025

    • DATETIME_FORMAT({Insert Date Field Name}, "D MMMM YYYY")

  • 31/12/25

    • DATETIME_FORMAT({Insert Date Field Name}, "D/M/YY")

  • 31/12/2025

    • DATETIME_FORMAT({Insert Date Field Name}, "D/M/YYYY")

Determining date starts, ends, or ranges

There are times when a date field needs to be represented as a range—like the start and end of a specific week. Combining DATETIME_FORMAT with DATEADD can pinpoint the start (Monday) and end (Friday) of the week based on any date. For more on DATEADD, see Working with date functions in Airtable.

1. Beginning of the week (Monday):

   "Week of " & DATETIME_FORMAT(DATEADD({Date},1-DATETIME_FORMAT({Date},'E'),'day'),'ll')

2. End of the week (Friday):

   "Week of " & DATETIME_FORMAT(DATEADD({Date}, 7 - DATETIME_FORMAT({Date}, 'E'), 'days'), 'll')

3. Full week range (Monday–Friday):

   "Week of " & DATETIME_FORMAT(DATEADD({Date},1-DATETIME_FORMAT({Date},'E'),'day'),'ll') & " - " & DATETIME_FORMAT(DATEADD({Date}, 7 - DATETIME_FORMAT({Date}, 'E'), 'days'), 'll')