Excel Date Time Format (DateTimeFormat)

Excel DateTimeFormats

CharacterDescriptionFormat ArgumentFormatted Display
=NOW()Shows current date and time=NOW()10/21/2018 8:01
dDay is displayed as a number, as one digit or as two digit, without a leading zero. (1-31)d8
ddDay is displayed as a number, as two digit, with a leading zero where applicable. (01-31)dd08
dddDay is abbreviated to three letters, viz. Sunday is displayed as Sun. (Sun-Sat)dddMon
ddddDay is displayed in its full format, viz. Sunday is displayed as Sunday. (Sunday-Saturday)ddddddMonday
wDay of the week is displayed as a number (1-7 for Sunday-Saturday).w, mmm d, yyyy2, Jul 8, 2013
ww Week of the year is displayed as a number. (1 to 54)ww28
mMonth is displayed as a number, as one digit or as two digit, without a leading zero - (1-12).  To use m as minute(s), it should appear immediately after the h or hh code, such as "h:m".m7
mmMonth is displayed as a number, as two digit, with a leading zero where applicable - (01-12). To use mm as minute(s), it should appear immediately after the h or hh code, such as "h:mm".mm07
mmmMonth name is abbreviated to three letters, viz. January is displayed as Jan. (Jan-Dec)mmmJul
mmmmMonth is displayed in its full name, viz. January is displayed as January. (January-December)mmmmJuly
qQuarter of the year is displayed as a number. (1 to 4)q3
yDay of the year is displayed as a number. (1 to 366)y189
yyYear is displayed as a number in two digits, viz. last 2 digits of the year are displayed. (00-99)yy13
yyyyYear is displayed as a number in four digits, viz. all digits of the year are displayed. (1900-9999)yyyy2013
Time separator. This separates hours, minutes & seconds while formatting time values. Changes m and mm to represent minutes instead of months.h:n:ss AMPM6:01:26 AM
hHour is displayed as a number, as one digit or as two digit, without leading zeros. (0-23)h6
HhHour is displayed as a number, as two digit, with a leading zero where applicable. (00-23)Hh06
mMinute is displayed as a number, as one digit or as two digit, without leading zeros -  (0 to 59). You can also use m code as minute (m is used for displaying month), for which it should appear immediately after the h or hh code, such as "h:m".m:ss1:26
mmMinute is displayed as a number, as two digit, with a leading zero where applicable -  (00 to 59). You can also use m code as minute (m is used for displaying month), for which it should appear immediately after the h or hh code, such as "h:m".mm:s1:26
SSecond is displayed as a number, as one digit or as two digit, without leading zeros. (0 - 59)h:mm:S6:13:6
SsSecond is displayed as a number, as two digit, with a leading zero where applicable.  (00 - 59)h:mm:Ss6:13:06
000Millisecondh:mm:ss.0008:27:11.2711
AM/PM, am/pmIf these codes are included in the format, the hour is displayed using a 12-hour clock, else the hour is based on the 24-hour format. Display will include AM, am, A or a for a time before noon, and PM, pm, P or p for a time 'from' and 'after' noon till 11:59 PM. While using AMPM, the case can be UPPER or lower, matching the string as defined by your system settings. AM/PM is the Default format.Hh:n:ss AM/PMAM
General Datestr = Format(SerialNo, "General Date")General Date7/31/2014 20:52
Long Datestr = Format(MyDate, "Long Date")Long DateThursday, July 31, 2014
Medium Datestr = Format(MyDate, "Medium Date")Medium Date31-Jul-14
Short Datestr = Format(MyDate, "Short Date")Short Date7/31/2014
Long Timestr = Format(MyTime, "Long Time")Long Time7:16:32 PM
Medium Timestr = Format(MyTime, "Medium Time")Medium Time7:16 PM
Short Timestr = Format(MyTime, "Short Time")Short Time19:16

Most commonly used Excel Date Time Formats and short codes for each type.

Excel DateTimeFormat Common Types

FormatResult
d-mmm-yy1-Jul-14
dd-mmm-yy01-Jul-14
dd-mmm-yyyy01-Jul-2014
dddd, dd mmmm, yyySunday, 21 October, 2018
dd-mm-yy21-10-18
dd-mm-yyyy21-10-2018
dd mmmm, yyyy21 October, 2018
mm/dd/yy10/02/18
mm/dd/yyyy10/02/2018
ddd, mmmm dd, yyyySun, October 21, 2018
m/d/yyyy9/2/2018
m/dd/yyyy9/02/2018
mm/d/yyyy09/2/2018
mm/dd/yyyy09/02/2018
dd-mmm21-Oct
dd-mmm-yy21-Oct-18
mmmm, yyyyOctober, 2018
yyyymmdd20181021