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

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:

="July "&DAY(TODAY())

July 9

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

Is there a way to do that?

Thanks

Microsoft Excel

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

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 questionLog 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

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
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(

Your help has saved me hundreds of hours of internet surfing.

fblack61

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

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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!

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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