--- title: "Formatting dates with DATETIME_FORMAT in Airtable" slug: "formatting-dates-using-datetimeformat-in-airtable" description: "This article covers how to use the DATETIME_FORMAT function to reformat date fields in Airtable." updated: 2026-04-10T21:28:43Z published: 2026-04-10T21:28:43Z --- > ## Documentation Index > Fetch the complete documentation index at: https://support.airtable.com/llms.txt > Use this file to discover all available pages before exploring further. # Formatting dates with DATETIME_FORMAT in Airtable | **Plan Availability** | All plan types | | --- | --- | | **Permissions** | - **Owner** / **Creator** - Can add, delete, duplicate, rename, and customize fields - **Editors** - Sort, filter, group, hide, and copy field URL - **Commenters / Read-only** - Copy field URL | | **Platform(s)** | Web/Browser, Mac app, and Windows app (with some additional limited support on mobile) | > [!CAUTION] > Note > > - To parse a datetime from a string, use the `DATETIME_PARSE()` function. [Learn more here](/docs/using-datetime-parse-formula). > - For more information on `DATETIME_FORMAT` and other formulas, check out [the formula field reference article](/docs/formula-field-reference). ## 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: ```plaintext 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](/docs/formatting-dates-using-datetimeformat-in-airtable#supported-datetime_format-specifiers) section below. Here's a quick example of how `DATETIME_FORMAT` transforms a date field from one format to another. ![Example showing DATETIME_FORMAT transforming a date from MM/DD/YYYY to DD-MM-YYYY](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/4402669524375Burritoexample.png) `{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})` ![Screenshot showing CONCATENATE formula with autonumber in the Experiment Name primary field](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/Screenshot 2025-09-16 at 12.59.49 PM.png) 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](/docs/using-a-formula-in-the-primary-field), concatenating "Experiment #" with the Autonumber field (Test ID). 2. *Test ID*—an [autonumber field](/docs/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](https://en.wikipedia.org/wiki/ISO_8601)—not easy to read. Here's what `CONCATENATE("Experiment #", {Test Date})` produces: ![Screenshot showing ISO-formatted date output that is hard to read](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/Screenshot 2025-09-16 at 1.01.01 PM.png) 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: ![Screenshot showing a readable, formatted experiment name using DATETIME_FORMAT](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/datetime_format_primary_field_example_2_2025.png) **Fixing a timezone difference in a formula's output** Occasionally, timestamps in `DATETIME_FORMAT` appear offset to GMT or another timezone. There are 2 ways to fix this: 1. The easiest way is to check "Use the same time zone (GMT) for all collaborators" in the date field's configuration menu. This can sometimes have downstream effects—use option 2 in those cases. ![Screenshot of the Use the same time zone setting in a date field's configuration menu](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/Screenshot 2025-09-16 at 12.53.57 PM.png) 2. A more flexible approach is to use the `SET_TIMEZONE` function inside `DATETIME_FORMAT`. For the full list of supported timezone identifiers, see [Supported SET_TIMEZONE timezones](/docs/supported-timezones-for-set-timezone). Here's an example for a team in the PST timezone: ```plaintext CONCATENATE("Experiment - ", DATETIME_FORMAT(SET_TIMEZONE({Test Date}, 'America/Los_Angeles'), 'M/DD/YYYY h:mm')) ``` This produces the same consistent results: ![Screenshot showing consistent formatted dates using SET_TIMEZONE inside DATETIME_FORMAT](https://cdn.airtable.document360.io/d0ee2ee4-3f78-47c7-b388-85e40be9fb89/Images/Documentation/Screenshot 2025-09-16 at 12.57.39 PM.png) ## Supported DATETIME_FORMAT specifiers > [!CAUTION] > 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](https://momentjs.com/docs/#/parsing/string-format) for date parsing. **The following DATETIME_FORMAT specifiers are supported:** | **Format specifier** | **Description** | **Example(s)** | | --- | --- | --- | | `M` | Month of the year, cardinal nos. 1–12 | 1 2 ... 11 12 | | `Mo` | Month of the year, ordinal nos. 1st–12th | 1st 2nd ... 11th 12th | | `MM` | Month of the year, cardinal nos. 01–12 | 01 02 ... 11 12 | | `MMM` | Month name, 3-letter abbreviation | Jan Feb ... Nov Dec | | `MMMM` | Full month name | January ... December | | `Q` | Quarter of the year, cardinal nos. 1–4 | 1 2 3 4 | | `Qo` | Quarter of the year, ordinal nos. 1st–4th | 1st 2nd 3rd 4th | | `D` | Day of the month, cardinal nos. 1–31 | 1 2 ... 30 31 | | `Do` | Day of the month, ordinal nos. 1st–31st | 1st 2nd ... 30th 31st | | `DD` | Day of the month, cardinal nos. 01–31 | 01 02 ... 30 31 | | `DDD` | Day of the year, cardinal nos. 1–365 | 1 2 ... 364 365 | | `DDDo` | Day of the year, ordinal nos. 1st–365th | 1st 2nd ... 364th 365th | | `DDDD` | Day of the year, cardinal nos. 001–365 | 001 002 ... 364 365 | | `d` | Day of the week, cardinal nos. 0–6 | 0 1 ... 5 6 | | `do` | Day of the week, ordinal nos. 0th–6th | 0th 1st ... 5th 6th | | `dd` | Day of the week, 2-letter abbreviation | Su Mo ... Fr Sa | | `ddd` | Day of the week, 3-letter abbreviation | Sun Mon ... Fri Sat | | `dddd` | Full day of the week | Sunday ... Saturday | | `e` | Day of the week (locale) | 0 1 ... 5 6 | | `E` | Day of the week (ISO) | 1 2 ... 6 7 | | `w` | Week of the year, cardinal nos. 1–53 | 1 2 ... 52 53 | | `wo` | Week of the year, ordinal nos. 1st–53rd | 1st 2nd ... 52nd 53rd | | `ww` | Week of the year, cardinal nos. 01–53 | 01 02 ... 52 53 | | `W` | Week of the year (ISO), cardinal nos. 1–53 | 1 2 ... 52 53 | | `Wo` | Week of the year (ISO), ordinal nos. 1st–53rd | 1st 2nd ... 52nd 53rd | | `WW` | Week of the year (ISO), cardinal nos. 01–53 | 01 02 ... 52 53 | | `YY` | Year, last 2 digits | 00 01 ... 98 99 | | `YYYY` | Full year | 2000 2001 ... 2098 2099 | | `gg` | Week year, last 2 digits | 00 01 ... 98 99 | | `gggg` | Full week year | 00 01 ... 98 99 | | `GG` | Week year (ISO), last 2 digits | 00 01 ... 98 99 | | `GGGG` | Full week year (ISO) | 2000 2001 ... 2098 2099 | | `A` | AM/PM (uppercase) | AM PM | | `a` | am/pm (lowercase) | am pm | | `H` | Hour, 24-hour clock, 0–23 | 0 1 ... 22 23 | | `HH` | Hour, 24-hour clock, 00–23 | 00 01 ... 22 23 | | `h` | Hour, 12-hour clock, 1–12 | 1 2 ... 11 12 | | `hh` | Hour, 12-hour clock, 01–12 | 01 02 ... 11 12 | | `m` | Minute | 0 1 ... 58 59 | | `mm` | Minute (zero-padded) | 00 01 ... 58 59 | | `s` | Second | 0 1 ... 58 59 | | `ss` | Second (zero-padded) | 00 01 ... 58 59 | | `S` | Fractional second (1 digit) | 0 1 ... 8 9 | | `SS` | Fractional second (2 digits) | 00 01 ... 98 99 | | `SSS` | Fractional second (3 digits) | 000 001 ... 998 999 | | `SSSS...SSSSSSSSS` | Fractional second (4–9 digits) | 000[0..] ... 999[0..] | | `Z` | Timezone offset from GMT, with colons | -07:00 -06:00 ... +06:00 +07:00 | | `ZZ` | Timezone offset from GMT, without colons | -0700 -0600 ... +0600 +0700 | | `X` | Unix timestamp | 1360013296 | | `x` | Unix millisecond timestamp | 1360013296123 | | `LT` | Preset time format: h:mm A | 6:30 PM | | `LTS` | Preset time format: h:mm:ss A | 6:30:45 PM | | `L` | Preset date format: MM/DD/YYYY | 06/08/2016 | | `l` | Preset date format: M/D/YYYY | 6/8/2016 | | `LL` | Preset date format: MMMM D, YYYY | June 8, 2016 | | `ll` | Preset date format: MMM D, YYYY | Jun 8, 2016 | | `LLL` | Preset datetime format: MMMM D, YYYY h:mm A | June 8, 2016 6:30 PM | | `lll` | Preset datetime format: MMM D, YYYY h:mm A | Jun 8, 2016 6:30 PM | | `LLLL` | Preset datetime format: dddd, MMMM D, YYYY h:mm A | Wednesday, June 8, 2016 6:30 PM | | `llll` | Preset datetime format: ddd, MMM D, YYYY h:mm A | Wed, Jun 8, 2016 6:30 PM | ## 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](/docs/working-with-date-functions). 1. Beginning of the week (Monday): ```plaintext "Week of " & DATETIME_FORMAT(DATEADD({Date},1-DATETIME_FORMAT({Date},'E'),'day'),'ll') ``` 2. End of the week (Friday): ```plaintext "Week of " & DATETIME_FORMAT(DATEADD({Date}, 7 - DATETIME_FORMAT({Date}, 'E'), 'days'), 'll') ``` 3. Full week range (Monday–Friday): ```plaintext "Week of " & DATETIME_FORMAT(DATEADD({Date},1-DATETIME_FORMAT({Date},'E'),'day'),'ll') & " - " & DATETIME_FORMAT(DATEADD({Date}, 7 - DATETIME_FORMAT({Date}, 'E'), 'days'), 'll') ```