+ Reply to Thread
Results 1 to 3 of 3

Ordinal dates and numbers in Excel 2007 or later

  1. #1
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Ordinal dates and numbers in Excel 2007 or later

    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.
    Last edited by Tony Valko; 05-28-2013 at 02:02 PM. Reason: I don't know how to spell
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  2. #2
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Ordinal dates and numbers in Excel 2007 or later

    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.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Ordinal dates and numbers in Excel 2007 or later

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1