Supported format specifiers for DATETIME_FORMAT
  • 21 Nov 2023
  • 4 Minutes to read
  • Dark
    Light
  • PDF

Supported format specifiers for DATETIME_FORMAT

  • Dark
    Light
  • PDF

Article Summary

Learn how various format specifiers can be used to format a date as a string in Airtable.

Understanding the DATETIME_FORMAT function

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the format of DATETIME_FORMAT(Datetime, 'format specifier') , in which the format specifier can be something like 'DD-MM-YYYY,' 'YYYY/MM/DD,' 'MM.DD,' etc. So for example, the formula DATETIME_FORMAT('10/22/2003', 'M') will return 10.

For more on how to use the DATETIME_FORMAT function in Airtable formulas, please see the article on Formulas and date fields. For more information on using DATETIME_FORMAT with timezones, please see the support article for Timezones and locales.

Supported format specifiers

Format Specifier

Description

Example(s)

 

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, three-letter abbreviation

Jan Feb ... Nov Dec

 MMMM 

Month name

January ... December

 

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 

 

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

 

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, two-letter abbreviation

Su Mo ... Fr Sa

 ddd 

Day of the week, three-letter abbreviation

Sun Mon ... Fri Sat

 dddd 

Day of the week

Sunday ... Saturday

 

Day of the week (Locale)

0 1 ... 5 6

 

Day of the week (ISO)

1 2 ... 6 7

 

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

 

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 two digits

00 01 ... 98 99

 YYYY 

Year

2000 2001 ... 2098 2099

 gg 

Week year, last two digits

00 01 ... 98 99

 gggg 

Week year

00 01 ... 98 99

 GG 

Week year (ISO), last two digits

00 01 ... 98 99

 GGGG 

Week year (ISO)

2000 2001 ... 2098 2099

 

Ante meridiem/post meridiem (majuscule)

AM PM

 a 

Ante meridiem/post meridiem (miniscule)

am pm

 H 

Hour, using a 24-hour clock from 0-23

0 1 ... 22 23

 HH 

Hour, using a 24-hour clock from 00-23

00 01 ... 22 23

 h 

Hour, using a 12-hour clock from 1-12

1 2 ... 11 12

 hh 

Hour, using a 12-hour clock from 01-12

01 02 ... 11 12

 

Minute

0 1 ... 58 59

 mm 

Minute

00 01 ... 58 59

 

Second

0 1 ... 58 59

 ss 

Second

00 01 ... 58 59

 

Fractional second

0 1 ... 8 9

 SS 

Fractional second

00 01 ... 98 99

 SSS 

Fractional second

000 001 ... 998 999

 SSSS ... SSSSSSSSS 

Fractional second

000[0..] ... 999[0..]

 

Timezone relative to GMT, inc. colons

-07:00 -06:00 ... +06:00 +07:00

 ZZ 

Timezone relative to GMT, not inc. colons

-0700 -0600 ... +0600 +0700

 

Unix timestamp

1360013296

 

Unix millisecond timestamp

1360013296123

LT

Preset timestamp format in the form of h:mm A

6:30 PM

LTS

Preset timestamp format in the form of h:mm:ss A

6:30:45 PM

L

Preset date format in the form of MM/DD/YYYY

06/08/2016

l

Preset date format in the form of M/D/YYYY

6/8/2016

LL

Preset date format in the form of MMMM D, YYYY

June 8, 2016

ll

Preset date format in the form of MMM D, YYYY

Jun 8, 2016

LLL

Preset datetime format in the form of MMMM D, YYYY h:mm A

June 8, 2016 6:30 PM

lll

Preset datetime format in the form of MMM D, YYYY h:mm A

Jun 8, 2016 6:30 PM

LLLL

Preset datetime format in the form of dddd, MMMM D, YYYY h:mm A

Wednesday, June 8, 2016 6:30 PM

llll

Preset datetime format in the form of ddd, MMM D, YYYY h:mm A

Wed, Jun 8, 2016 6:30 PM

Common formatting examples

ISO 8601

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

  • For example, 2025-12-31 15:00:00.000, represents the 10th of July 2020 at 3 p.m. It’s important to note that there is no time zone offset specified and the time is generally assumed to be in UTC format.

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

America Dates

Date and time notation in the United States formats the date in month–day–year format:

  • 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 dates

Date and time notation in the United Kingdom formats the date in day–month–year format:

  • 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”)

Format specifier clarifications

The format specifiers w and W behave slightly differently from one another. W is the "ISO week of year" (see Wikipedia for more details). w is for the "locale week of year" (in our case, according to the Gregorian calendar in GMT). 2/11/21 falls on the 6th ISO week and on the 7th Gregorian week. Behind the scenes, Airtable uses the moment library for date parsing. All of the tokens are documented here.


Was this article helpful?