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

GertFoot

New Member
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.

Rick Rothstein

MrExcel MVP
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.

Replies
1
Views
2K
Replies
16
Views
56K

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.

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

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