Format Dates and Times in Your Excel Reports - dummies

By Michael Alexander

Custom number formatting isn’t just for numbers. You can improve the look of your Excel dashboards and reports by formatting dates and times as well. As you can see in this figure, you use the same dialog box to apply date and time formats using the Type input box.


This figure demonstrates that date and time formatting involves little more than stringing date-specific or time-specific syntax together. The syntax used is fairly intuitive. For example, ddd is the syntax for the three-letter day, mmm is the syntax for the three-letter month, and yyyy is the syntax for the four-digit year.

There are several variations on the format for days, months, years, hours, and minutes. It’s worthwhile to take some time and experiment with different combinations of syntax strings.

This table lists some common date and time format codes you can use as starter syntax for your reports and dashboards.

Common Date and Time Format Codes
Format Code 1/31/2014 7:42:53 PM Displays As
M 1
Mm 01
mmm Jan
mmmm January
mmmmm J
dd 31
ddd Thu
dddd Thursday
Yy 14
yyyy 2014
mmm-yy Jan-14
dd/mm/yyyy 31/01/2014
dddd mmm yyyy Thursday Jan 2014
mm-dd-yyyy h:mm AM/PM 01-31-2014 7:42 PM
h AM/PM 7 PM
h:mm AM/PM 7:42 PM
h:mm:ss AM/PM 7:42:53 PM