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.


DOWNLOAD WORKBOOK

STEP 1: To see how the formatting works, pick any one on the table. Then press CTRL + 1 to open the Format Cells Dialog.

STEP 2: Over here you can see the Custom Date Format used, in our example, it’s mmmm-yyyy and there is a sample of February-1979 at the top to show you how it looks like.

Try it out for the different formats!

Here is the list of codes that you can use:

CODEOUTPUTDATE/TIMEAPPEARS AS
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

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn