Custom Date and Time Formatting Symbols and Examples


You can make almost any date and time format you want with Excel’s custom formatting. For example, I don’t like the dashes in between date characters and I do like to use a three letter abbreviation for month, so my favorite custom format in the US is,

mmm d, yyyy

Apr 1, 2016

Date and Time Symbols

Symbol Results
General  For PCs this is the number of days from the beginning of the 1900 century. The last day’s time appears as the decimal portions of 24 hours.
To see this enter =Date(2016,4,1) in a cell and press Enter. Reformat the cell to General and you will see the number 42461.
d Day 1 to 31; no leading zero
dd Day 01 to 31; leading zero
ddd Day as an abbreviation (Mon-Sun)
dddd Day as a full name (Monday-Sunday)
m Month 1 to 12; no leading zero
mm Month 01 to 12; leading zero
mmm Three-letter month abbreviation; Jan to Dec
mmmm Full name of month; January to December
yy Two-digit year; 00 to 99
yyyy Full year number; 2000 to 2078
 H  Hours; 0 to 24; no leading zero
 Hh  Hours; 00 to 24; leading zero
m Minutes; 0 to 59; no leading zero
mm Minutes; 00 to 59; leading zero
s Seconds; 0 to 59; no leading zero
ss Seconds; 00 to 59; leading zero
[ ] Hours greater than 24, minutes greater than 60, or seconds greater than 60.
A/P Displays the hour, using the AM/PM 12-hour clock.
-_ Places dash divider between parts
/ Places slash divider between parts
: Places colon divider between parts.

* m characters following an h are interpreted as minutes.

Sample Custom Date and Time Formats

Format Display
dddd Friday
mmmm d, yyyy April 1, 2016
d mmm,yy 1 Apr, 16
yy/mm/dd 16/04/01
[RED] d mmm,yy 1 Jun, 16 (in red)