A few days back, I have explained about how to convert text into a date.
But sometimes we need to use a custom date format which is not as per standard date formats.
As you know...
...we are blessed with custom formatting so you can create a custom date with a regular date serial number.
The benefit of a custom format is you don't need to change the actual date serial number, that mean you can 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 present dates in a unique way.
How to use these Custom Date Formats
To all the formats which I have mentioned above you need to follow below steps.
- Select the cell where you want to apply the format.
- Now, press right click and select "Format Cell". Or, you can also use a keyboard shortcut Ctrl + 1 to open format cell options.
- After that select custom and enter the format in the input bar and click OK.
#1 - 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 below format.
“It’s” ddd
#2 - Day Number
Just like the day name if you want to get the day number you can use this custom format.
“Day” d
#3 - Month Name
This custom date format will help you to get month name for your date value. And, you can also add some custom text as per your requirement.
“Current Month is:” mmmm
#4 - Date with a Day Name
This format gives help you a date with a day name in the end.
dd-mmm-yyyy (ddd)
#5 - Long Textual Date
And, if you want a long textual date you can do this by using this 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.
Must Learn Excel Tutorials
- The [Secret Formula] to Calculate Age in Excel Using Date of Birth
- How to use DATEDIF [Mysterious] Function in Excel + Sample File
- The Top 3 Ways To Get End of the Month Date in Excel
- 5 Methods Excel Experts Use To Get [Extract] a Month Name from a Date
- SUMIFS is the Best Method to SUM Values Between Two Dates in Excel
About the Author
Puneet Gogia
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
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