Steve_Brady

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:

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

Is there a way to do that?

Thanks

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())="July "&DAY(TODAY())

**Display:**

July 9July 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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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")

="July "&DAY(TODAY())&IFERROR(CHO

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")

="July "&DAY(TODAY())&CHOOSE(MOD(

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.

=TEXT(TODAY(),"mmmm")&" "&TEXT(TODAY(),"DD")&IF(AN

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

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

Thanks

Rob H

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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!

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.

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.

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.

="July "&DAY(TODAY())&IF(OR(DAY(T