Supported DATETIME_FORMAT format specifiers in Airtable

Updated on 16 Feb 2024
4 Minutes to read

Print
Share
Dark
Light
PDF

Article summary

Did you find this summary helpful?

Thank you for your feedback

Learn how various format specifiers can be used to format a date as a string in Airtable.

Introduction

Plan availability	All plan types
Platform(s)	Web/Browser, Mac app, and Windows app
Related reading	Getting started with formulas

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

NOTE
The 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")

Was this article helpful?

What's Next

Using the DATETIME_PARSE() formula in Airtable

Table of contents

Introduction
Understanding the DATETIME_FORMAT function
Supported DATETIME_FORMAT specifiers
Common formatting examples