- 16 Feb 2024
- 4 Minutes to read
- Print
- DarkLight
- PDF
Supported DATETIME_FORMAT format specifiers in Airtable
- Updated on 16 Feb 2024
- 4 Minutes to read
- Print
- DarkLight
- PDF
Learn how various format specifiers can be used to format a date as a string in Airtable.
Introduction
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
NOTEThe 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) |
---|---|---|
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 |
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")