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