Date formula (excel 97 (at work))

[nacrious]

making up a spreadsheet of weather related data and recording a date of an event in a cell. I would like the cell to auto come up with the st, nd, rd, th, bit of a date when I enter date digit only into cell so finished cell has 2 nd, 19 th 31 st etc
thanks

[KingMartin]

Hello,

automatic? Not a formula? May be a bit confusing for the user.
However, here's your code, belongs in Worksheet Class Module:
<pre>Private Sub Worksheet_Change(ByVal Target As Range)
'this works only for B1:B100
Set myRange = [B1:B100] 'Put here your range
If Intersect(myRange, Target) Is Nothing Then Exit Sub
If Not IsDate(Target) Then Exit Sub

Select Case Right(Day(Target), 1)
Case 1
Target.Value = Format(Target, "mmmm d""st"", yyyy")
Case 2
Target.Value = Format(Target, "mmmm d""nd"", yyyy")
Case 3
Target.Value = Format(Target, "mmmm d""rd"", yyyy")
Case Else
Target.Value = Format(Target, "mmmm d""th"", yyyy")
End Select

End Sub</pre>



Put any valid date in the cells B1:B100.

Kind regards
Martin

[rory]

Hi Martin,
Your code doesn't take into account the 11th, 12th and 13th. I think you're better off looking at the whole day value and specifying each anomalous number.
Regards,

[KingMartin]

Ooops, sorry, forgot about it, English is not my mother tongue... <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
This should work, then:
<pre>Private Sub Worksheet_Change(ByVal Target As Range)
'this works only for B1:B100
Const FormatST = "mmmm d""st"", yyyy"
Const FormatND = "mmmm d""nd"", yyyy"
Const FormatRD = "mmmm d""rd"", yyyy"
Const FormatTH = "mmmm d""th"", yyyy"

Set myRange = [B1:B100] 'Put here your range
If Intersect(myRange, Target) Is Nothing Then Exit Sub
If Not IsDate(Target) Then Exit Sub

Select Case Right(Day(Target), 1)

Case 1
If Day(Target) <> 11 Then
Target.Value = Format(Target, FormatST)
Else
Target.Value = Format(Target, FormatTH)
End If

Case 2
If Day(Target) <> 12 Then
Target.Value = Format(Target, FormatND)
Else
Target.Value = Format(Target, FormatTH)
End If

Case 3
If Day(Target) <> 13 Then
Target.Value = Format(Target, FormatRD)
Else
Target.Value = Format(Target, FormatTH)
End If
Case Else
Target.Value = Format(Target, FormatTH)
End Select

End Sub</pre>



Thanks for reminding me <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

[KingMartin]

To be complete, here's a formula I use to convert a number into an ordinal number:

=A16&IF(OR(VALUE(RIGHT(A16,2))={111213}),"th",IF(O R(VALUE(RIGHT(A16))={123}),CHOOSE(RIGHT(A16),"st", "nd","rd"),"th"))

[macropod]

Hi,

For a previous discussion of ordinal dating, see the thread starting at <post#= 323027>post 323027</post#>.

Cheers