Avatar of Steve_Brady
Steve_Brady
Flag for United States of America asked on

Add (st, nd, rd, th) suffix to a date (or any number) in Excel

Hello,

In Excel (2010), is there a way to add a suffix (st, nd, rd, th) to a number and specifically to a date?

For example, with today being Monday, July 9, 2012, the following formula returns the value shown:

          Formula:
                    ="July "&DAY(TODAY())
   

          Display:
                    July 9


but suppose instead, you want the displayed date to be in the form:

          July 9th

Is there a way to do that?

Thanks
Microsoft Excel

Avatar of undefined
Last Comment
Steve_Brady

8/22/2022 - Mon
SOLUTION
ltlbearand3

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Saqib Husain

Try this formula

="July "&DAY(TODAY())&IF(OR(DAY(TODAY())=1,DAY(TODAY())=21,DAY(TODAY())=31),"st",IF(OR(DAY(TODAY())=2,DAY(TODAY())=22),"nd",IF(OR(DAY(TODAY())=3,DAY(TODAY())=23),"rd","th")))
SOLUTION
Steve_Brady

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Saqib Husain

Another formula for 2007 +

="July "&DAY(TODAY())&IFERROR(CHOOSE(DAY(TODAY()),"st","nd","rd","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","st","nd","rd","th","th","th","th","th","th","th"),"st")
Saqib Husain

A bit more compact

="July "&DAY(TODAY())&CHOOSE(MOD(DAY(TODAY()),10)*(INT(DAY(TODAY())/10)<>1)+1,"th","st","nd","rd","th","th","th","th","th","th","th")
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rob Henson

This is longer than other suggestions but covers all dates rather than specific to July.

=TEXT(TODAY(),"mmmm")&" "&TEXT(TODAY(),"DD")&IF(AND(RIGHT(TEXT(DAY(TODAY()),"00"),1)="1",DAY(TODAY())<>11),"st",IF(AND(RIGHT(TEXT(DAY(TODAY()),"00"),1)="2",DAY(TODAY())<>12),"nd",IF(AND(RIGHT(TEXT(DAY(TODAY()),"00"),1)="3",DAY(TODAY())<>13),"rd","th")))

This converts the current day to format "mmmm dd##" where ## is the suffix.

The reference to TODAY() could be changed to refer to a cell.

Thanks
Rob H

PS This will be a text value so won't be as readily used in calculations referring to it.
Rob Henson

To explain:

If day value ends with 1 but isn't 11 then "st",
If day value ends with 2 but isn't 12 then "nd",
If day value ends with 3 but isn't 13 then "rd",
Otherwise "th"

Just checking on various dates, looks like it will look better if the "DD" in the second TEXT(TODAY()) is changed to just "D" other wise it ends up with 01st and 02nd etc.

Thanks
Rob H
Rob Henson

Apologies to Steve_Brady. My suggestion is virtually the same as yours. I had skipped over it looking at the snippet window as being VBA.

Thanks
Rob H
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve_Brady

ASKER
One of the greatest things, imo, about EE, is being able to see so many different ways of tackling the same problem.

Many thanks for the great and varied responses!
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve_Brady

ASKER
Note re best solution selection:

I selected my own solution as being the best only because it is the clearest in my mind and is therefore the one I will want to find if/when I refer back to this thread in the future.  I'm sure the same thing can be said for each solution and the person who posted it.

The implication I want to avoid however, is that I think mine is the "best." As mentioned earlier, there are several ways of arriving at the same point, and therefore, selecting the "best" is really a matter of preference.  Maybe EE should change the terminology to: "preferred" solution.

Thanks again for the input.