# Off Topic > Tips and Tutorials >  >  Ordinal dates and numbers in Excel 2007 or later

## Tony Valko

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.
```

----------


## macropod

hi Biff,

In a similar vein, the following function takes an integer value and returns it as an ordinal in any Office application.



```
Please Login or Register  to view this content.
```


With only minor mods, it could be made to apply the ordinal scheme to a custom number format for an Excel cell.

----------


## Tony Valko

Thanks, Paul!

I was kind of hoping someone would post the code for a Worksheet_Change event macro that could be used in Excel 2003 and earlier versions.

----------

