Tips Excel : Create Custom Date and Time Formats

You can enhance your worksheet display of dates and times by creating your own custom date and time formatting. Date and time formats determine how Excel displays data values and time values in a range. For a date, the date format determines whether Excel displays the value with the year, month, date, or all three, and whether you see short values such as Fri or long values such as Friday.

For a time, the time format determines whether Excel displays the value with the hour, minute, second, or all three. Although the built-in date and time formats are fine for most applications, you might need to create your own custom formats. For example, you might want to display just the day of the week (for example, Friday). To do this, you can create custom date and time formats. You can do so either by editing an existing format or by creating your own format. The formatting syntax and symbols are explained in the Tip.

How to Custom Date Format  Excel

1 Select the range you want to format.
2 Click the Home tab.
3 In the Number group, click the dialog box.

The Format Cells dialogbox appears with theNumber tab selected.
● If you want to base your custom format on an existing format, click either the Date or Time category and then click the format.
4. Click Custom.

5. Type the symbols and text that define your custom format.
6. Click OK.

● Excel applies the custom format.

Customize It! 

Use the symbols in the following table to build your custom date and time formats.
Symbol Description
d 
Displays a day number without a leading zero (1–31).
dd 
Displays a day number with a leading zero (01–31).
ddd 
Displays a three-letter day abbreviation (Mon).
dddd 
Displays a full day name (Monday).
m 
Displays a month number without a leading zero (1–12).
mm 
Displays a month number with a leading zero (01–12).
mmm 
Displays a three-letter month abbreviation (Aug).
mmmm 
Displays a full month name (August).
yy 
Displays a two-digit year (00–99).
yyyy 
Displays a full year (1900–2078).
h 
Displays an hour without a leading zero (0–24).
hh
Displays an hour with a leading zero (00–24).
m 
Displays a minute without a leading zero (0–59).
mm 
Displays a minute with a leading zero (00–59).
s 
Displays a second without a leading zero (0–59).
ss 
Displays a second with a leading zero (00–59).

Categories: Excel 2010