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

by Jason Morrell
January 5, 2022

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.

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:

## 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 yyyyd"rd" mmmm yyyyd"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?

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.

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

more from

## 3 Ways to Build an Absolute Reference Into Excel Formulas

3 Ways to Build an Absolute Reference Into Excel Formulas

## How to Analyse Sales Performance in Excel

How to Analyse Sales Performance in Excel

JOIN THE DISCUSSION