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

Dynamic Data Validation List What´s a dynamic data validation drop down list in Excel, you say?Well, as you add new data into your Excel Table, your drop down list automatically gets updated.That is a cool feature and it means that you do not need to update your data validation source reference each ...
Show The Percent of Parent Column Total With Excel...  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 PARENT COLUMN TOTAL calculation.This option will immediately calculate the percentages for you from a table filled with numbers suc...
Excel Table: Styles There are lots of different Table Styles that you can choose from to spice up your Excel Table.  You can also customize your own style if you don't like any of the default Excel styles.To access the Table Styles, you simply need to click inside your Table and then the Table T...
Show Averages With an Excel Pivot Table A Pivot Table is the most powerful feature within Excel as it allows you to analyze your data in many different ways, all with a press of a button.The Summarize Values By option allows you to choose a type of calculation (Sum, Count, Average, Max, Min, Count Numbers Produ...