Link to home
Start Free TrialLog in
Avatar of Steve_Brady
Steve_BradyFlag 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
SOLUTION
Avatar of ltlbearand3
ltlbearand3
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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")
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")
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.
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
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.