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.
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:
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:
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?
Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item!
ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.
Enjoy the rest of your day!