Articles in this section
Articles in this section ▾
Formulas and date fields
Working with date functions
Calculate the difference between two dates
Supported unit specifiers for DATETIME_DIFF
Supported format specifiers for DATETIME_FORMAT
Using DATETIME_PARSE() formula

Supported format specifiers for 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.

The following is a table of the supported format specifiers:

Format Specifier Description Example(s)
M 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
Q 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 
D 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
d 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
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-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
W 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
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-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
m Minute 0 1 ... 58 59
mm Minute 00 01 ... 58 59
s Second 0 1 ... 58 59
ss Second 00 01 ... 58 59
S 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..]
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 timestamp 1360013296
x 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

 

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?

Related articles