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

 

[ajm5807]

Thats sorted it...thanks



Thanks to you both for all the help

 

[Biz]

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

Nice code Rick

Biz