Are you desperate to stick with old date conventions?
Rather than displaying a date in the conventional way such as 3 June 2022 (or June 3 2022 in the US) would you prefer to display your dates like 3rd June 2022 like your Nana did?
If so, read on, because in this post I'll give you 2 ways to create these kinds of dates.
And here is a working example you can download.
RELATED POST:
How to create your any custom date format in Excel
1. Here are the ENDING rules
1. Use 'st' for day numbers 1, 21 and 31.
2. Use 'nd' for day numbers 2 and 22.
3. Use 'rd' for day numbers 3 and 23.
4. Use 'th' for every other day number (4-20 and 24-30).
2. Method #1 - IF and OR formula
The idea here is to create a text string that creates each part of the date.
Three IF / OR functions are created, one for each of the first 3 rules above that determines wihich ending to use.
This is then appended to the end of the day number to create for example, '2nd', before the month name and 4 digit year are appended.
Here is the entire formula:
=DAY(A2) &
IF(OR(DAY(A2)=1, DAY(A2)=21, DAY(A2)=31), "st",
IF(OR(DAY(A2)=2, DAY(A2)=22), "nd",
IF(OR(DAY(A2)=3, DAY(A2)=23), "rd", "th")))
& " " & TEXT(A2, "mmmm") & " " & YEAR(A2)
3. Method #2 - Conditional Formatting
The idea, using Conditional Formatting, is to create a condition & result pairing for each of the rules above, where each result is a self-contained custom date format.
Here is the method:
1. Select the cell range containing your dates.
2. On the Home ribbon, click Conditional Formatting | New Rule | Use a formula to determine which cells to format.
On Mac, on the Home ribbon, click Conditional Formatting | New Rule | Change Style to Classic then in the box underneath, choose Use a formula to determine which cells to format.
3. In the box labelled 'Format values where this formula is true' type:
=OR(DAY(A2)=1, DAY(A2)=21, DAY(A2)=31)
4. Click the Format button.
5. In the dialog, select the Number tab and click the Custom category.
6. Clear the Type box and type:
d"st" mmmm yyyy
7. Click OK.
8. Repeat steps 3-7 and create these additional rules:
=OR(DAY(A2)=2, DAY(A2)=22)
=OR(DAY(A2)=3, DAY(A2)=23)
=OR(
AND(DAY(A2)>=4,DAY(A2)<=20),
AND(DAY(A2)>=24,DAY(A2)<=30))
and these associated formats:
d"nd" mmmm yyyy
d"rd" mmmm yyyy
d"th" mmmm yyyy
The selected dates will reformat automatically.
The big advantage of method #2 is that Excel retains the serial numbers for each date, so you can continue to use them in calculations later on.
The downside of creating dates using text strings is that you cannot use 'text' dates in calculations - they are for display purposes only.
4. Key Takeaways
5. What next?
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE
