Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
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
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!
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
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
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