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

GertFoot

New Member
Joined
Feb 23, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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...
VBA 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.
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Threads
1,130,167
Messages
5,640,539
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top