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

Excel Hyperlinks: Buttons Excel´s hyperlink capability is amazing but many people don't use it as they don't know its full capabilities. With a hyperlink you can link an object/text to open an existing file on your desktop, go to a website, open up an email to a specific contact or go to a cell within ...
Excel Number Formats – Thousands & Mill... Large numbers in Excel can be formatted so they can be shown in "Thousands" or "Millions". By using the Format Cells dialogue box shortcuts CTRL+1, you will need to select CUSTOM and then enter one comma to show Thousands or two commas to show Millions. You can even add som...
Fix Incorrect Formatting Using Flash Fill In Excel   Flash Fill in Excel is a new feature that was introduced in Excel 2013.  One of the cool uses of Flash Fill is to fix incorrect formatting in your text automatically. Ever had the scenario where your data is formatted differently? Example: First names starting wit...
Custom Number Formats in Excel A custom number format in Excel can have up to four different sections in the following order: Positive format; Negative format; Zero format; Text format   You can specify different format codes for each section as long as they are separated by a semicolon. So you ca...