Insert a number with "st", "nd","rd","th"

[Mansfieldexcel]

In my worksheet I have the following:

A2 is a date e.g. 01Jan20 to 31Dec20
B2 is the day of the week based on A2 e.g. Mon or Wed or Sun. Formula in this cell is =TEXT(WEEKDAY(A2), "ddd")
C3 is the occurrence of that day in any given month, based on A2 e.g. 1 or 2 or 3 or 4 or 5 ie the first wednesday of a month or second wednesday of a month or fifth friday of a month etc. Formula in this cell is: =INT((DAY(A2)-1)/7)+1

Examples of data are:

01Jan20 Wed 1
08Jan20 Wed 2
31Jan20 Fri 5
01Feb20 Sat 1
08Feb20 Sat 2
31Dec20 Thu 5

I have been able to identify which days of month, in a new column - D3, are "st", "nd","rd","th" with the following formula =LOOKUP(DAY(A2),{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"})

01Jan20 Wed 1 st
08Jan20 Wed 2 nd
31Jan20 Fri 5 th
01Feb20 Sat 1 st
08Feb20 Sat 2 nd
31Dec20 Thu 5 th

But this hasn't helped as the formula identifies which days of week are; "st", "nd","rd","th" and not the "occurrence of that day" in any given month. (if that makes sense)

The result I am looking for is as follows;

01Jan20 Wed 1 1st
08Jan20 Wed 2 2nd
31Jan20 Fri 5 5th
01Feb20 Sat 1 1st
08Feb20 Sat 2 2nd
31Dec20 Thu 5 5th


I trust the above makes some sense and thank you for your time and consideration.

[Fluff13]

How about
=DAY(A2)&LOOKUP(DAY(A2),{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"})

[Mansfieldexcel]

Thank you for the quick response much appreciated. However, the solution you propose does not appear to give the desired result. I do get day of week and the appropriate "st", "nd","rd","th" but
the solution I am looking for is;

01Jan20 Wed 1 so the 1 is appended with st. This the first Wednesday in Jan. Required Result 1st
etc
08Jan20 Wed 2 so the 2 is appended with nd. This is the second Wednesday in Jan. Requested Result 2nd
etc
31Jan20 Fri 5 so the 5 is appended with th. This is the fifth Friday in Jan. Required Result 5th
01Feb20 Sat 1 so the 1 is appended with st. This is the first Saturday in Feb. Required Result 1st
etc
08Feb20 Sat 2 so the 2 is appended with nd. This is the second Saturday in Feb. Required Result 2nd
etc...
31Dec20 Thu 5 so the 5 is appended with th. This is the fifth Thursday in Dec. Required Result 5th

So the required "st", "nd","rd","th" are based upon 1 or 2 or 5 or 1 or 2 or 5 in the desired solution and not on the actual day of month.

Thank you again for your time and patience.

[Fluff13]

In that case, how about
=C2&LOOKUP(C2,{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"})

[Mansfieldexcel]

Perfect. Thank you so much for your time. All worked ok. This thread can now be closed.

[Fluff13]

You're welcome & thanks for the feedback.

Tomark the thread as sloved, please select Thread Tools from the menu link above the OP and select SOLVED.