Is there a better way to put dates in Ordinal format?
Posted by JAF on December 29, 2000 6:50 AM
I was asked recently if there was any way to get Excel to put dates in an Ordinal format (1st, 2nd, 3rd etc).
I couldn't work out any way of doing it using the normal formatting codes, but did come up with the following macro to get what my user needed.
Dim Day_Number As Integer
For Each cell In Selection
Day_Number = Day(cell)
If Day_Number = 2 Or Day_Number = 22 Then
cell.NumberFormat = "d""nd"" mmmm yyyy"
ElseIf Day_Number = 3 Or Day_Number = 23 Then
cell.NumberFormat = "d""rd"" mmmm yyyy"
ElseIf Day_Number = 1 Or Day_Number = 21 Or Day_Number = 31 Then
cell.NumberFormat = "d""st"" mmmm yyyy"
cell.NumberFormat = "d""th"" mmmm yyyy"
The drawback with this method is that the macro has to loop through each cell, which on a large spreadsheet and (very) slow PC can be a little time consuming, even with this fairly simple bit of code.
Is there a better/easier/quicker way of formatting a range of dates to display in Ordinal format?