Free Practice WorkbookTable of Contents
Custom Date Formats in Excel
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 workbookNumber-Formats-Dates.xlsx
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:
| CODE | OUTPUT | DATE/TIME | APPEARS AS |
| 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 |
HELPFUL RESOURCE:
Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
