Supported DATETIME_FORMAT format specifiers in Airtable

Prev Next
Plan AvailabilityAll plan types
Required permissionsOwner / Creator - Can add, delete, duplicate, rename, and customize fields
Platform(s)Web/Browser, Mac app, and Windows app (with some additional limited support on mobile)

The DATETIME_FORMAT function overview

The DATETIME_FORMAT function allows you to reformat data from a date-type field into a string that meets your specifications. It is expressed like DATETIME_FORMAT(Datetime, 'format specifier'), meaning the format specifier can be something like 'DD-MM-YYYY,' 'YYYY/MM/DD,' 'MM.DD,' etc.

As an  example, the formula DATETIME_FORMAT('10/22/2003', 'M') returns 10.

Note

Supported DATETIME_FORMAT specifiers

Note

The format specifiers "w" and "W" behave differently. "W" is the "ISO week of year," while "w" is for 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 the Moment.js for date parsing.

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 example:

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 example:

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 example:

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")