MENU
    Supported DATETIME_FORMAT format specifiers in Airtable
    • 16 Feb 2024
    • 4 Minutes to read
    • Dark
    • PDF

    Supported DATETIME_FORMAT format specifiers in Airtable

    • Dark
    • PDF

    Article summary

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

    Introduction

    Plan availability

    All plan types

    Platform(s)

    Web/Browser, Mac app, and Windows app

    Related reading

    Understanding the DATETIME_FORMAT function

    Understanding the DATETIME_FORMAT

    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 DATETIME_FORMAT specifiers

    Supported format specifiers

    NOTE

    The format specifiers w and W behave slightly differently from one another. W is the "ISO week of year.” 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.

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


    Was this article helpful?