# 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
ltlbearand3

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

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

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.