Spell out date
Private Sub Ordinal_Dates()
Dim cell As Range
For Each cell In Selection
If IsDate(cell.Value) Then
Select Case Day(cell.Value)
Case 1, 21, 31
cell.NumberFormat = "d""st day of ""mmm, yyyy"
Case 2, 22
cell.NumberFormat = "d""nd day of ""mmm, yyyy"
Case 3, 23
cell.NumberFormat = "d""rd day of ""mmm, yyyy"
Case 4 To 20, 24 To 30
cell.NumberFormat = "d""th day of ""mmm, yyyy"
End Select
End If
Next cell
End Sub
Or sheet event code to format as you enter.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
For Each cell In Target
If IsDate(cell.Value) Then
Select Case Day(cell.Value)
Case 1, 21, 31
cell.NumberFormat = "d""st day of ""mmm, yyyy"
Case 2, 22
cell.NumberFormat = "d""nd day of ""mmm, yyyy"
Case 3, 23
cell.NumberFormat = "d""rd day of ""mmm, yyyy"
Case 4 To 20, 24 To 30
cell.NumberFormat = "d""th day of ""mmm, yyyy"
End Select
End If
Next cell
End Sub
Gord Dibben MS Excel MVP
On Tue, 8 Jul 2008 08:44:03 -0700, ProudFoot
wrote:
Thanks,
This helps for most of the dates, what should I add if the date is the 1st,
2nd, 3rd, 21st, 22nd.....?
"FSt1" wrote:
hi
not a formula but 3 formulas with concatinators.
date in b2?......
=DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2)
regards
FSt1
"ProudFoot" wrote:
Is there a formula or VBA code to make "July 8, 2008" automatically say "8th
day of July, 2008"?
Any help is appreciated.
|