2 Ways to Create Excel Dates Like 3rd June 2022 (1st, 2nd, 3rd, 4th etc.)

AUTHOR:  JASON MORRELL 

Jason Morrell

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

  • There are 2 ways to create dates that display as 1st, 2nd, 3rd, 4th etc. Use an IF + OR formula or create a series of conditional formatting rules.

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!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Check the RELATED articles below

Read More
10 Steps to a Perfect Print Page Setup in Excel With Auto-Repeat Headings and Controlled Page Breaks
Read More
Excel Auditing Checklist: 2 Auto Features and 4 Tools to Quickly Identify and Rectify Errors BEFORE You Publish
Read More
Supercharge Your Cut and Paste: Popular Methods, Shortcuts and 2 Smarter Approaches
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Office Mastery
>