Starting in Excel 2007 the options for conditional formatting were greatly expanded. In previous versions we were limited to just 3 conditions. In Excel 2007 and later there are almost no limits on the number of conditions (limited to system memory) and we can also use custom number formats.
These new capabilities allow us to use conditional formatting to display ordinal dates and numbers. For example, if we enter the date 5/27/2013 we can get Excel to display that date as May 27th 2013 or, if we enter the integer 10 we can get Excel to display that integer as 10th.
It's important to know that the ordinal suffix is for DISPLAY purposes only. The true underlying value of the cell will still be the date 5/31/2013 or the integer 10.
Here's how to do it.
Start Excel 2007 or later.
Use conditional formatting to display an ordinal date.
Create the custom formats...
Select the cell you want to format. Let's assume that's cell A1.
Right click>Format Cells>Number tab>Custom
In that little box under Type: enter the format code:
mmm d"st" yyyy
Click OK
Repeat that process for these format codes:
mmm d"nd" yyyy
mmm d"rd" yyyy
mmm d"th" yyyy
Now, apply the conditional formatting...
Since there is a maximum of 31 days in any month we only need to deal with the ordinal suffix for the numbers 1 to 31 (days of the month).
With cell A1 still selected...
Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format
Enter this formula in the box below:
=OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31)
Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code: mmm d"st" yyyy
OK>OK
New rule>Use a formula to determine which cells to format
Enter this formula in the box below:
=OR(DAY(A1)=2,DAY(A1)=22)
Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code: mmm d"nd" yyyy
OK>OK
New rule>Use a formula to determine which cells to format
Enter this formula in the box below:
=OR(DAY(A1)=3,DAY(A1)=23)
Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code: mmm d"rd" yyyy
OK>OK
New rule>Use a formula to determine which cells to format
Enter this formula in the box below:
=OR(AND(DAY(A1)>=4,DAY(A1)<=20),AND(DAY(A1)>=24,DAY(A1)<=30))
Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code: mmm d"th" yyyy
OK out
Try it out. Enter some date in cell A1. The cell can also contain a formula that returns a date, for example:
=TODAY()
=EDATE(NOW(),12)
-------------------------------------------------------
Use conditional formatting to display an ordinal number.
Create the custom formats...
Select the cell you want to format. Let's assume that's cell A1.
Right click>Format Cells>Number tab>Custom
In that little box under Type: enter the format code:
0"st"
Click OK
Repeat that process for these format codes:
0"nd"
0"rd"
0"th"
With cell A1 still selected...
Right click>Format cells>Number tab>Custom
From the drop down list on the right select the code: 0"th"
OK out
Now, apply the conditional formatting...
In Excel versions 2007 and earlier there is a bug in the MOD(...) function such that it returns a #NUM! error on very large numbers. I don't remember the exact number where it starts but it's in the billions. So this conditional formatting will not work on very large numbers in excel 2007. The MOD(...) bug has been fixed in Excel 2010 and later.
Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format
Enter this formula in the box below:
=MOD(A1,10)=1
Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code: 0"st"
OK>OK
New rule>Use a formula to determine which cells to format
Enter this formula in the box below:
=MOD(A1,10)=2
Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code: 0"nd"
OK>OK
New rule>Use a formula to determine which cells to format
Enter this formula in the box below:
=MOD(A1,10)=3
Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code: 0"rd"
OK>OK
New rule>Use a formula to determine which cells to format
Enter this formula in the box below:
=AND(MOD(A1,100)>10,MOD(A1,100)<21)
Click the Format button
Select the Number tab>Custom
From the drop down list on the right select the code: 0"th"
OK out
Try it out. enter some numbers in cell A1.
-------------------------------------------------------
Here's a generic formula for returning the ordinal suffix of any integer with the limitation of the MOD(...) function described above.
=MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)
A1 = 13
=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)
Returns 13th
Here's the origin of that formula:
http://tinyurl.com/OrdinalNums
-------------------------------------------------------
Ordinal ranks...
You can use the conditional formatting procedure above to display ordinal ranks or you could use the above formula like this:
A1:A5 =
13
51
69
71
38
This formula entered in B1 and copied down to B5:
=RANK(A1,A$1:A$5)&MID("thstndrdth",MIN(9,2*RIGHT(RANK(A1,A$1:A$5))*(MOD(RANK(A1,A$1:A$5)-11,100)>2)+1),2)
Results =
Please Login or Register to view this content.
Bookmarks