Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date formula (excel 97 (at work))

    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

  2. #2
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula (excel 97 (at work))

    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
    Regards,
    Martin

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date formula (excel 97 (at work))

    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,
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula (excel 97 (at work))

    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>
    Regards,
    Martin

  5. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formula (excel 97 (at work))

    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"))
    Regards,
    Martin

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Date formula (excel 97 (at work))

    Hi,

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

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •