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.


m Displays the month as a number 18/02/1979 2
mm Displays the month as a number with leading zeros 18/02/1979 02
mmm Displays the month in abbreviated form 18/02/1979 Feb
mmmm Displays the month in full form 18/02/1979 February
mmmmm Displays the first letter of the month 18/02/1979 F
d Displays the day as a number 18/02/1979 18
dd Displays the day as a number with leading zeros 01/02/1979 01
ddd Displays the day in abbreviated form 18/02/1979 Thu
dddd Displays the day in full form 18/02/1979 Thursday
yy Displays the last two digits of the year 18/02/1979 79
yyyy Displays all the digits of the year 18/02/1979 1979
mmmm d, yyyy Displays the month, the date and the year 18/02/1979 February 18, 1979
mmmm-yyyy Displays the month-year 18/02/1979 February-1979
“Today is” dddd Displays a custom text for the Today function 11/06/2015 Today is Thursday
h Displays the hour as a number 9:55:13 9
hh Displays the hour as a number with leading zeros 9:55:13 09
AM/PM Displays the hour indicating AM or PM 9:55:13 AM

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

Autosum an Array of Data in Excel When you have an array of data in Excel with Totals at the bottom and to the right of the data, you can quickly fill in the Totals with the Autosum button. STEP 1: Highlight your data including the "Totals" row and column; STEP 2: Click the Autosum button (under the Home or...
Text to Columns – Emails If you have a data set with text consisting of names and email addresses that are wrapped inside a parenthesis, like: John (   ...then you can use the Text to Columns feature in Excel to take out the email addresses and put them in a separate ...
Find Errors with Go to Special Constants Say you have a data set and want to make sure that each column contains what it is supposed to. For example, say you have a column which contains Dates and you want to check that there are no cells which contain Text. You can easily check this by highlighting that column an...
Conditionally Formatting A Drop Down List In a previous post I showed you how to Create a Drop Down List in a Table.    We are now going to take this concept one level further and apply some conditional formatting to the drop down data validation list. This is useful if you want to highlight when a job is completed, c...