Plan Availability | All plan types |
Required permissions | Owner / Creator - Can add, delete, duplicate, rename, and customize fields |
Platform(s) | Web/Browser, Mac app, and Windows app (with some additional limited support on mobile) |
The DATETIME_FORMAT function overview
The DATETIME_FORMAT function allows you to reformat data from a date-type field into a string that meets your specifications. It is expressed like DATETIME_FORMAT(Datetime, 'format specifier')
, meaning the format specifier can be something like 'DD-MM-YYYY,' 'YYYY/MM/DD,' 'MM.DD,' etc.
As an example, the formula DATETIME_FORMAT('10/22/2003', 'M')
returns 10
.
Note
Check out our Date field article to learn more about using the DATETIME_FORMAT function in formulas.
Check out our Timezones and locales article to learn more about using the DATETIME_FORMAT function with timezones.
Supported DATETIME_FORMAT specifiers
Note
The format specifiers "w" and "W" behave differently. "W" is the "ISO week of year," while "w" is for the "locale week of year" (Gregorian calendar in GMT). 2/11/21 falls on the 6th ISO week and on the 7th Gregorian week. Airtable uses the Moment.js for date parsing.
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 |
Common formatting examples
ISO 8601 example:
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 example:
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 example:
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")