Ordinal Numbers

[ajm5807]

Can cells be formatted to show ordinal numbers in things like dates etc

e.g.

01/09/11 to show as 1st September 2011 (sorry for the American users but Im British lol)

I know how to format the date as above but not to show "st"...or "nd", "rd" or "th" as appropriate

Many thanks in advance

 

[T. Valko]

Can cells be formatted to show ordinal numbers in things like dates etc

e.g.

01/09/11 to show as 1st September 2011 (sorry for the American users but Im British lol)

I know how to format the date as above but not to show "st"...or "nd", "rd" or "th" as appropriate

Many thanks in advance

No, you can't format for ordinals.

You'd have to use another cell to convert the date to an ordinal.

A1 = 1/9/2011 (d/m/y)

This formula entered in B1:

=DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(DAY(A1)-11,100)>2)+1),2)&" "&TEXT(A1,"mmmm")&" "&YEAR(A1)

Returns the TEXT string: 1st September 2011

 

[ajm5807]

Ok, thanks for the "get around"

 

[T. Valko]

No, you can't format for ordinals.

You'd have to use another cell to convert the date to an ordinal.

A1 = 1/9/2011 (d/m/y)

This formula entered in B1:

=DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(DAY(A1)-11,100)>2)+1),2)&" "&TEXT(A1,"mmmm")&" "&YEAR(A1)

Returns the TEXT string: 1st September 2011

Here's another formula that uses less functions but is still about the same length:

=DAY(A1)&MID("stndrdthstndrdthst",MATCH(DAY(A1),{1,2,3,4,21,22,23,24,31})*2-1,2)&" "&TEXT(A1,"mmmm")&" "&YEAR(A1)

 

[T. Valko]

Ok, thanks for the "get around"

You're welcome!

 

[Rick Rothstein]

If you need the formatting to be dynamic (that is, it updates with changes in the cell value just like Cell Formatting would), we can use VB event code to provide that functionality. Give this a try... right click the name tab at the bottom of the worksheet containing the cell or cells you want to format in the way you indicated and select "View Code" from the popup menu that appears. Now, copy/paste the following code into the code window that opened up...

Private Sub Worksheet_Calculate()
  Dim Ordinal As String, Cell As Range
  Const CellsToMonitor As String = "C:D"
  For Each Cell In Intersect(ActiveSheet.UsedRange, Range(CellsToMonitor))
    If IsDate(Cell.Value) Then
      Ordinal = Mid$("thstndrdthththththth", 1 - 2 * ((Day(Cell.Value)) Mod 10) * (Abs((Day(Cell.Value)) Mod 100 - 12) > 1), 2)
      Cell.NumberFormat = "d""" & Ordinal & """ mmmm yyyy"
    Else
      Cell.NumberFormat = "General"
    End If
  Next
End Sub

Now, there is one thing you have to change to match your setup. The CellsToMonitor constant (the Const statement) sets the cell or cells that you want to apply your special format to. My example assignment is C:E meaning the cell format would be applied to any date entered into Column C, D or E. You can assign any valid range to this constant. For example, to monitor just Column F, you would use "F:F"; for Row 3, you would use "3:3", for both ranges A3:C8 and F5:K7 you would use "A3:C8,F5:K7"; and so on. Okay, go back to your worksheet and enter a date, or a formula that outputs a date, anywhere in the range you assigned to the CellsToMonitor constant and it will be formatted with your special format.

 

[ajm5807]

Thanks for that example Rick, I dont need that yet but will keep it in case I do, however when Ive tried it...nothing happens

I left it as "C:D" as the sample code you supplied and input dates as 4/9/11 for example in columns C and D but they just appear as that 4/9/11

Could you check it, maybe Im doing something wrong

Many thanks for your assistance so far

 

[Rick Rothstein]

when Ive tried it...nothing happens

I left it as "C:D" as the sample code you supplied and input dates as 4/9/11 for example in columns C and D but they just appear as that 4/9/11

Did you follow the instructions I gave at the beginning telling you how to install the code? This is not a macro, so it cannot be placed in a standard module where macro go... it must be installed in a worksheet module (which is where event code goes). Just so you know, the code was tested before I posted it.

 

[ajm5807]

Yeah, pasted it in the Code for that sheet

 

[Rick Rothstein]

Yeah, pasted it in the Code for that sheet

Hmm, it didn't work for me either. The reason is entering a constant in a cell that is not referenced in any formulas does not cause the sheet to calculate. The reason my test worked during development is because I had the Volatile function NOW() on my sheet and Volatile functions always cause a recalculation whenver anything takes place on a sheet.

Okay, the reason I went with the Calculate event was I wanted the code to work for typed-in text and formulas that returned dates as well. If you only need this fuctionality for typed-in dates, then use this (Change) event code instead...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Ordinal As String, Cell As Range
  Const CellsToMonitor As String = "C:D"
  For Each Cell In Target
    If IsDate(Cell.Value) Then
      Ordinal = Mid$("thstndrdthththththth", 1 - 2 * ((Day(Cell.Value)) Mod 10) * (Abs((Day(Cell.Value)) Mod 100 - 12) > 1), 2)
      Cell.NumberFormat = "d""" & Ordinal & """ mmmm yyyy"
    Else
      Cell.NumberFormat = "General"
    End If
  Next
End Sub