Starting with Excel 2007, it is possible to specify the number format as part of conditional formatting. This note shows how to leverage that capability to add the appropriate suffix to an ordinal number (e.g., 1st, 2nd, 3rd, 4th, etc.)

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0122%20ordinal%20number%20suffixes.shtml

I found a quite interesting way of doing this, though I don’t recall where I got it from. Using one simple formula without any conditional formatting, one can achieve a similar result:

Assuming value resides in cell A1,

Or define the rules as

If the units digit is 0 or 4-9 or the tens digit is 1, use ‘th’, ELSE ‘st’ for units digit 1, ‘nd’ for units digit 2, and ‘rd’ for units digit 3.

fzz –

Which fails for eleven (11st) twelve (12nd) and thirteen (13rd) :roll:

…mrt@Michael, did you test it other than in your mind?

With 1 in A1, =A1+1 in A2 and A2 filled down to A24, B1 containing the formula

=IF(OR(MOD(A1,10)={0;4;5;6;7;8;9},INT(MOD(A1,100)/10)=1),”th”,CHOOSE(MOD(A1,10),”st”,”nd”,”rd”))

and B1 filled down to B24, I get the following results.

1st

2nd

3rd

4th

5th

6th

7th

8th

9th

10th

11th

12th

13th

14th

15th

16th

17th

18th

19th

20th

21st

22nd

23rd

24th

fzz –

Hmm. That would be/must be “Guilty as charged, Your Honor.” I was reading your input and looking at the preceding formula. They don’t go together. Now, the people ask, why is it I can read an Excel formula better than I can read a declarative sentence?

Nice formulation. Thanks and apologies.

…mrt…mrt…mrt@Muntz,

I was happy to have been a party to the development of that formula. In case you (or anyone else for that matter) are interested in the genesis for that formula, here is the original thread where it came into existence at….

http://groups.google.com/group/microsoft.public.excel.misc/browse_frm/thread/d3e93dbaaf5c1064?hl=en&q=biff+ordinal+group:*excel*+author:rothstein

Look at messages #2 through #11 (you will have to click in the list on the left to see the 11th message). By the way, message #2 contains a one-liner UDF implementing this functionality in case anyone needs it in that format.

CAUTION….

Be careful with the link I just posted… I notice that this blog’s comment processor did not turn all the entire text into a hyperlink, so you will do better by copy/pasting it into your browser.

FORMAT QUARTERS TOO!

Thanks for this… This technique could also be used to format dates in the case of quarters(eg 2001Q1, 2002Q2,…)… Something I was looking for and never found a better solution for!

Here’s how to achieve it, assuming your date is in D6

conditional format rule 1:

=OR(MONTH(D6)=1;MONTH(D6)=2;MONTH(D6)=3)

custom format to use

yyyy “Q1?

conditional format rule 2:

=OR(MONTH(D6)=4;MONTH(D6)=5;MONTH(D6)=6)

custom format to use

yyyy “Q2?

conditional format rule 3:

=OR(MONTH(D6)=7;MONTH(D6)=8;MONTH(D6)=9)

custom format to use

yyyy “Q3?

conditional format rule 4:

=OR(MONTH(D6)=10;MONTH(D6)=11;MONTH(D6)=12)

custom format to use

yyyy “Q4?

Up to now I had to use a macro that was performing a similar test on each cell of the selected range before applying the corresponding formating (eg a different format for each cell). This solution is more elegant.

For david’s qtr output:

year(cell)&”Q”&ceiling(month(cell)/3,1)

1/31/2012 2012Q1

2/29/2012 2012Q1

3/31/2012 2012Q1

4/30/2012 2012Q2

5/31/2012 2012Q2

6/30/2012 2012Q2

7/31/2012 2012Q3

8/31/2012 2012Q3

9/30/2012 2012Q3

10/31/2012 2012Q4

11/30/2012 2012Q4

12/31/2012 2012Q4

I created a simple way of doing this by using IF and MOD try it for the result.

=IF(AND(MOD(B2,100)>10,MOD(B2,100)<14),B2&"th",IF(MOD(B2,10)=1,B2&"st",IF(MOD(B2,10)=2,B2&"nd",IF(MOD(B2,10)=3,B2&"rd",IF(AND(MOD(B2,100)10),B2&”th”,B2&”th”)))))

1st

2nd

3rd

4th

5th

6th

7th

8th

9th

10th

11th

12th

13th

14th

15th…….goes till 100th, 111th, 121st……..Nth