Format date...

[Davers]

Is there some sort of format that will show the date as July 9th, 2004? I can get the date to show as July 9, 2004. But I don't know how to account for the different days, such as the 1st, 2nd, 3rd, 4th...etc...

Dave

 

[Scott Huish]

You may need to adjust your range, but something like this should work. Put this in the Worksheet Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsDate(Target.Value) Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Dim DateSuffix As String
Target.NumberFormat = ""
Select Case Day(Target.Value)
Case 1, 21, 31
DateSuffix = "\s\t"
Case 2, 22
DateSuffix = "\n\d"
Case 3, 23
DateSuffix = "\r\d"
Case 4 To 30
DateSuffix = "\t\h"
End Select
Application.EnableEvents = False
Target.NumberFormat = "mmmm d" & DateSuffix & ", yyyy"
Application.EnableEvents = True
End Sub

 

[Davers]

Whoa!! Thanks hotpepper....I appreciate it!

Have a good weekend!

Dave