Excel VBA - Legal language problem - "the 22nd, 24th and 26th Friday in 2021" - don't know where to start

[GertFoot]

Hi there, first post.

I have a year's worth (365 days) of dates in a range that can start at any point in a year but I need to show the WEEKDAYNUM equivalent for each WEEKDAY. For example, if the range starts at Monday 1/10/2022 I need to be able to show that that particular Monday is in fact the second Monday in 2022. I need to calculate this for all days of the week. It is for a legal document so I guess there is a precedent for describing dates, for example, in custody arrangements as the 4th, 6th, 8th, 10th and 12th Thursday (etc) in a calendar year - even though the contract does not start at the beginning of a calendar year.

I'm pretty stuck at this point, I hope this makes sense, I'm not even sure what terms I should be using to describe my problem. If I was working in calendar years counting the weekdays would be a trivial problem. I guess I could extend the range to the beginning of the range year but that seems really clunky. I'm hoping for a VBA formula or function.

Any help, suggestions or links to leads would be super appreciated.

 

[Rick Rothstein]

Function d(r As Range)
    Dim dd As String, w As Integer
    If IsDate(r.Value) Then
        w = Format(r.Value, "ww")
        If Weekday(DateSerial(Year(r.Value), 1, 1), vbSunday) > Weekday(r.Value, vbSunday) Then w = w - 1
        Select Case w
            Case 1, 21, 31, 41, 51: dd = "st"
            Case 2, 22, 32, 42, 52: dd = "nd"
            Case 3, 23, 33, 43: dd = "rd"
            Case Else: dd = "th"
        End Select
        d = w & dd & Format(r.Value, " dddd of yyyy")
    End If
End Function

Just noting that your entire Select Case block can be replaced by this single line of code...

dd = Mid$("thstndrdthththththth", 1 - 2 * (w Mod 10) * (Abs(w Mod 100 - 12) > 1), 2)

Note: This single line of code will work with any number, not just the number of days in a month.