Custom date formats change how a date looks without changing the underlying value, so the cell stays a real date you can still sort, filter, and calculate with. Select the cells, press Ctrl+1, choose Custom, and type your format in the Type box.
The building blocks are d for day number, ddd for the short day name, dddd for the full day name, m for month number, mmm for the short month name, mmmm for the full month name, and yy or yyyy for the year. Combine them freely: dd-mmm-yyyy (ddd) shows 15-Jan-2026 (Thu), and mmmm yyyy shows January 2026.
To add your own words, put them in double quotes — “Due:” dd-mmm-yyyy displays Due: 15-Jan-2026. The alternative is the TEXT function, =TEXT(A1,”dd-mmm-yyyy”), but that returns text rather than a date, so it won’t sort chronologically.
Sometimes we need to use a custom date format which is not one of the standard date formats. As you know, we are blessed with custom formatting, so you can create a custom date format for a regular date serial number.
The benefit of a custom format is that you don’t need to change the actual date serial number, which means you can use those dates further in calculations.
So today, I’d like to share with you some of my favorite custom date formats which can help you uniquely present dates.
How to Use these Custom Date Formats
For all the formats which I have mentioned below, you need to follow these steps.
- Select the cell where you want to apply the format.
- Now, right-click and select “Format Cells”. Or you can also use the keyboard shortcut Ctrl + 1 to open format cell options.
- After that, select “Custom” in the Category list, enter the format in the “Type” box and click OK.
Day Name
This one is my favorite. If you are working on some dates where you want to get the day name only, this custom date format can help you.
"It's" dddd
And, if you want to get the day name in short form, you can use the below format.
"It's" ddd
Day Number
Just like the day name, if you want to get the day number, you can use this custom format.
"Day" d
Month Name
This custom date format will help you to get the month name for your date value. And, you can also add some custom text as per your requirement.
"Current Month is:" mmmm
Date with a Day Name
This format gives you a date with a day name at the end.
dd-mmm-yyyy (ddd)
Long Textual Date
And, if you want a long textual date, you can do this by using the below format.
"Today is Day" d", Month" m "& Year" yyyy.
Now tell me one thing. Do you have any format which you use? Share with me in the comment section.
More Useful Custom Date Formats
Here are a few more formats that I use a lot. For a date like 5 November 2026, they show:
- Full date with the day name: Thursday, November 5, 2026 (format below this list).
- Month and year only: Nov-2026.
dddd, mmmm d, yyyy
mmm-yyyy
The codes work like this: d is the day number (dd adds a leading zero), ddd and dddd are the short and full day names, m and mm are the month number, mmm and mmmm are the short and full month names, and yy and yyyy are the year. Any text you want to show goes in double quotes.
Use These Formats in a Formula with TEXT
If you want the formatted date as text in another cell, for example, to join it with other text, use the same format codes in the TEXT function:
=TEXT(A1,"dddd")
="Report for "&TEXT(A1,"mmmm yyyy")
The first formula returns the day name of the date in A1, and the second one returns text like “Report for November 2026”. Unlike a custom format, TEXT returns text, so you can’t use the result in date calculations.
Frequently Asked Questions
Why is my custom date format not working?
The cell probably has a date stored as text, and number formats only work on real dates (numbers). Convert the text to a date first, for example with DATEVALUE or Data ➜ Text to Columns.
Why does the cell show ####?
The column is too narrow for the formatted date, which happens often with long formats like dddd, mmmm d, yyyy. Make the column wider, or double-click the column border to AutoFit it.
Can I show the day name in another language?
Yes. Add a locale code at the start of the format, like [$-fr-FR]dddd for French. Excel then shows the day and month names in that language.
How could we add a letter at the end ie.: 02/17/2018-S
Consider explicit, int’l date formats
21/009/2017 dd/”0″MM/yyyy
009.21.2017 “0”MM.dd.yyyy
2017-009-21 yyyy-“0″mm-dd
Info @ https://goo.gl/cbKPje
#1World1Date1Day
#CoCoCoCo
Hey Puneet,
exactly where are you writing this custom format?
In MS Excel where you select your Number Formats, there is a CUSTOM option at the end of the list which also includes Currency, Date, General and more. Select CUSTOM, then in the TYPE field, use the options above, such as dd/”0″MM/yyyy for 21/009/2017. Hope this helps.
#yyyy0MMdd Info @ https://goo.gl/cbKPje #1World1Date1Day
I’m liking this idea and will start using it. There will be push back but it will eventually catch on.
Pushback?! Indeed. So please use it where you can, chat it up, Spread the word!
#1World1Date1Day
#1Monde1Date1Jour
great job,puneet
Thanks