Contents x
    Supported format specifiers for DATETIME_FORMAT
    • 06 Jul 2022
    • 3 Minutes to read
    • Print
    • Dark
      Light
    Contents

    Supported format specifiers for DATETIME_FORMAT

    • Updated on 06 Jul 2022
    • 3 Minutes to read
    • Print
    • Dark
      Light
    Article Summary

    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.

    The following is a table of the supported format specifiers:

    Format Specifier
    		Description
    		Example(s)
     M Month of the year, cardinal nos. 1-121 2 ... 11 12
     Mo Month of the year, ordinal nos. 1st-12th1st 2nd ... 11th 12th
     MM Month of the year, cardinal nos. 01-1201 02 ... 11 12
     MMM Month name, three-letter abbreviationJan Feb ... Nov Dec
     MMMM Month nameJanuary ... December
     Q Quarter of the year, cardinal nos. 1-41 2 3 4 
     Qo Quarter of the year, ordinal nos. 1st-4th1st 2nd 3rd 4th 
     D Day of the month, cardinal nos. 1-311 2 ... 30 31 
     Do Day of the month, ordinal nos. 1st-31st1st 2nd ... 30th 31st
     DD Day of the month, cardinal nos. 01-3101 02 ... 30 31
     DDD Day of the year, cardinal nos. 1-3651 2 ... 364 365
     DDDo Day of the year, ordinal nos. 1st-365th1st 2nd ... 364th 365th
     DDDD Day of the year, cardinal nos. 001-365001 002 ... 364 365
     d Day of the week, cardinal nos. 0-60 1 ... 5 6
     do Day of the week, ordinal nos. 0th-6th0th 1st ... 5th 6th
     dd Day of the week, two-letter abbreviationSu Mo ... Fr Sa
     ddd Day of the week, three-letter abbreviationSun Mon ... Fri Sat
     dddd Day of the weekSunday ... 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-531 2 ... 52 53
     wo Week of the year, ordinal nos. 1st-53rd1st 2nd ... 52nd 53rd
     ww Week of the year, cardinal nos. 01-5301 02 ... 52 53
     W Week of the year (ISO), cardinal nos. 1-531 2 ... 52 53
     Wo Week of the year (ISO), ordinal nos. 1st-53rd1st 2nd ... 52nd 53rd
     WW Week of the year (ISO), cardinal nos. 01-5301 02 ... 52 53
     YY Year, last two digits00 01 ... 98 99
     YYYY Year2000 2001 ... 2098 2099
     gg Week year, last two digits00 01 ... 98 99
     gggg Week year00 01 ... 98 99
     GG Week year (ISO), last two digits00 01 ... 98 99
     GGGG Week year (ISO)2000 2001 ... 2098 2099
     A Ante meridiem/post meridiem (majuscule)AM PM
     a Ante meridiem/post meridiem (miniscule)am pm
     H Hour, using a 24-hour clock from 0-230 1 ... 22 23
     HH Hour, using a 24-hour clock from 00-2300 01 ... 22 23
     h Hour, using a 12-hour clock from 1-121 2 ... 11 12
     hh Hour, using a 12-hour clock from 01-1201 02 ... 11 12
     m Minute0 1 ... 58 59
     mm Minute00 01 ... 58 59
     s Second0 1 ... 58 59
     ss Second00 01 ... 58 59
     S Fractional second0 1 ... 8 9
     SS Fractional second00 01 ... 98 99
     SSS Fractional second000 001 ... 998 999
     SSSS ... SSSSSSSSS Fractional second000[0..] ... 999[0..]
     Z 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
     X Unix timestamp1360013296
     x Unix millisecond timestamp1360013296123
    LTPreset timestamp format in the form of h:mm A6:30 PM
    LTSPreset timestamp format in the form of h:mm:ss A6:30:45 PM
    LPreset date format in the form of MM/DD/YYYY06/08/2016
    lPreset date format in the form of M/D/YYYY6/8/2016
    LLPreset date format in the form of MMMM D, YYYYJune 8, 2016
    llPreset date format in the form of MMM D, YYYYJun 8, 2016
    LLLPreset datetime format in the form of MMMM D, YYYY h:mm AJune 8, 2016 6:30 PM
    lllPreset datetime format in the form of MMM D, YYYY h:mm AJun 8, 2016 6:30 PM
    LLLLPreset datetime format in the form of dddd, MMMM D, YYYY h:mm AWednesday, June 8, 2016 6:30 PM
    llllPreset datetime format in the form of ddd, MMM D, YYYY h:mm AWed, Jun 8, 2016 6:30 PM

    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?
    What's Next