Custom date formats in Excel allow you to display only certain parts of the date.

Say you had a date of 18/02/1979, which coincides to be my birthday 🙂

You can use the Format Cells dialogue box to show only the number 18, the day that corresponds to that date (Sunday), the month as a number on in abbreviated form and the year in two or four digits.

You cam also mix and match to create a custom date format or even enter a custom text that would show something like: Today is Sunday

You can download the following workbook which shows you the different formats that you can use and see the tutorial below of how this can be easily achieved.


mDisplays the month as a number18/02/19792
mmDisplays the month as a number with leading zeros18/02/197902
mmmDisplays the month in abbreviated form18/02/1979Feb
mmmmDisplays the month in full form18/02/1979February
mmmmmDisplays the first letter of the month18/02/1979F
dDisplays the day as a number18/02/197918
ddDisplays the day as a number with leading zeros01/02/197901
dddDisplays the day in abbreviated form18/02/1979Thu
ddddDisplays the day in full form18/02/1979Thursday
yyDisplays the last two digits of the year18/02/197979
yyyyDisplays all the digits of the year18/02/19791979
mmmm d, yyyyDisplays the month, the date and the year18/02/1979February 18, 1979
mmmm-yyyyDisplays the month-year18/02/1979February-1979
“Today is” ddddDisplays a custom text for the Today function11/06/2015Today is Thursday
hDisplays the hour as a number9:55:139
hhDisplays the hour as a number with leading zeros9:55:1309
AM/PMDisplays the hour indicating AM or PM9:55:13AM

Number Formats - Dates

If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

How To Make Negative Red Numbers In Excel When you are working with lots of different numbers in Excel, you sometimes want your numbers to stand out by showing them in a negative red number with a minus sign in the front.To do this you need to select your numbers and press CTRL+1 to bring up the Format dialogue box. ...
Excel Table Slicers Slicers were introduced with Pivot Tables in Excel 2010 and they allowed us to select items to filter with beautiful interactive buttons.You can see the power of Slicers and the different ways you can format them in these posts:
Show The Percent Of With Excel Pivot Tables  Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF calculation.It will display the value of one item (the Base Field) as the percentage of another item (the Base Item).This optio...
Turn Text To Values With Paste Special Values Many times you would have received data from your IT system which is formatted wrong!  Well a gazillion times if you work in a mid sized firm 🙂When you try and sum the values you get a count rather than a sum, that is because Excel reads the data as text rather than a value....