Is there a better way to put dates in Ordinal format?

Posted by JAF on December 29, 2000 6:50 AM

Hiya

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.

Sub Format_Ordinal()
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"
Else
cell.NumberFormat = "d""th"" mmmm yyyy"
End If
Next cell
End Sub

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?

JAF

Posted by Tim Francis-Wright on December 29, 2000 8:59 AM

This is a somewhat better way

I think that a macro is the only way to go here.
I made a couple of changes to your macro that
speed it up--using Application.ScreenUpdating
and setting "d""th"" mmmm yyyy" as the initial
default format. (On a range of about 11000
dates, it ran in just under 2 seconds, versus
just under 11 seconds for your macro.)

Good luck!

Sub Format_Ordinal() ' with modifications
Dim Day_Number%
Application.ScreenUpdating = False
Selection.Cells.NumberFormat = "d""th"" mmmm yyyy"

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"
End If
Next Cell
Application.ScreenUpdating = True

End Sub

Good luck!

Posted by Celia on December 29, 2000 4:29 PM

Re: This is a somewhat better way

A macro is probably the best method, but just by way of note, J. Walkenbach in his book Excel 2000 Formulas provides this formula :-

=DAY(A1)&IF(INT(MOD(DAY(A1),100)/10)=1, "th", IF(MOD(DAY(A1),10)=1, "st",IF(MOD(DAY(A1),10)=2,"nd", IF(MOD(DAY(A1),10)=3, "rd","th"))))& " " &TEXT(A1,"mmmm, yyyy")

Celia

Posted by Tim Francis-Wright on December 29, 2000 10:13 PM

Very nice!

That would be fastest of all, of course, assuming
that the user doesn't need the date values anymore,
(One could, I suppose, strip out substrings of th/rd/nd/st and
then apply DATEVALUE to the remainder.)

Thanks for the tip!

--tim f-w

Posted by Dave on December 30, 2000 3:05 AM

Re: Very nice!

Hi all

Why not use a combo of both VBA and a WorksheetFormula, maybe something like:

Sub OrdinalDate()
Dim StopRow As Long
StopRow = [A65536].End(xlUp).Row
Application.ScreenUpdating = False

With Range("B1")
.FormulaR1C1 = "=DAY(RC[-1])&IF(INT(MOD(DAY(RC[-1])" _
& ",100)/10)=1, ""th"", IF(MOD(DAY(RC[-1]),10)=1, ""st""," _
& "IF(MOD(DAY(RC[-1]),10)=2,""nd"", IF(MOD(DAY(RC[-1]),10)" _
& "=3, ""rd"",""th""))))& "" "" &TEXT(RC[-1],""mmmm, yyyy"")"
.AutoFill Range("B1:B" & StopRow)
End With

Range("B1:B" & StopRow).Copy
Range("A1").PasteSpecial xlPasteValues
Range("B1:B" & StopRow).Clear
Application.ScreenUpdating = True
End Sub

Dave

OzGrid Business Applications