Date Formats

The FORMATDATE function returns a formatted date-time string as defined by a date format pattern. For example:

FORMATDATE(TODAY(),"dd/MM/YY")

Date patterns are character strings that comprise a combination of pattern fields and literal text.

  1. Pattern fields are replaced with date and time data as the date is formatted. Characters A and Z, and a and z, are reserved as pattern characters (see below).
  2. Literal text may include:
    • Any characters delimited by single vertical quotes ('xxxx')
    • Any characters (other than the pattern characters A and Z, and a and z, described above, unless they are delimited by single vertical quotes)
    • Adjacent single quotes (''), which represent a literal single quote, either inside or outside quoted text

Examples of the pattern fields you can specify in a date format pattern are shown in the following table:

Pattern Description Example Values
d day 1
dd day 01
w week 8, 27
ww week 08, 27
M month 9, 12
MM month 09, 12
MMM month Sep
MMMM month September
Y full year 2019
YY short year 19
H hour(24) 0, 23
HH hour(24) 00, 23
h hour(12) 1, 12
hh hour(12) 01, 12
m minute 8, 59
mm minute 08, 59
s second 8, 12
ss second 08, 12
a ... aaa period AM/PM, am/pm
GG era AD
GGGG era (wide) Anno Domini
EEE weekday Tue
EEEE weekday (wide) Tuesday
z ... zzz time zone (short) PST
zzzz time zone (long) Pacific Standard Time

Example date format patterns are shown in the following examples:

Pattern Result (specific to locale)
yyyyy.MMMMM.dd GGG hh:mm aaa 02019.August.06 AD 12:10 PM
K:mm a, z 0:00 PM, PST
hh 'o''clock' a, zzzz 12 o'clock PM, Pacific Daylight Time
yyyy.MM.dd G 'at' HH:mm:ss zzz 2019.08.06 AD at 13:05:55 PDT
EEE, MMM d, ''y Tue, August 8, '19

For more information, see the section "Date Format Patterns": ICU Date/Time Format Codes List