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

Supported format specifiers for DATETIME_FORMAT

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