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.

 

[StephenCrump]

Welcome to the Forum!

Am I over-simplifying this?

=TEXT(A1,"dddd") will give you the day.
=INT((A1-DATE(YEAR(A1)-1,12,25))/7) will give you the incidence (1st, 2nd, 3rd etc) in that calendar year

Or do you want the opposite direction, e.g. determine the 20th Tuesday in 2021?

 

[GertFoot]

Thankyou for your kind and informative welcome StephenCrump!

I do believe you have answered my question

Please permit me to make a fool of myself while I try to decipher this: =INT((A1-DATE(YEAR(A1)-1,12,25))/7)

INT will cast the result to an int
A1 Eg 1/2/2021 - take the date in A1 and subtract something.
Something: DATE(YEAR(A1)-1,12,25) - making Xmas day in the previous year, so 12/25/2020
So 1/2/2021 - 12/25/2020 / 7

I am afraid I am lost. If you would care to take a moment I would be very grateful.

Otherwise thank you very much for your rapid help.

Cheers

 

[Dave Patton]

I am not exactly sure what you want to show; a few more examples of what you require will help.
Do you require Ordinals like st nd etc. ?
Please consider the example shown below.
We can make the formula more concise and readable.
You can paste the example to a clean sheet; click on the icon below the F(x).
I used 2 named arrays
Named Array DOWS ={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}Mon 10-Jan-22
Named Array Ord ={1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"}

Date and Time 2021.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Start	Weekday	Interval	1	2	3	4	5	6
3	1-Jan-22	Monday	2		Mon 10-Jan-22
4	15-Feb-22	Tue	2				Tue 08-Mar-22		Tue 22-Mar-22
5
6
7
8
9	1-Jan-22	Mon	2		2nd Mon 10-Jan-2022
6b

Cell Formulas
Range	Formula
E3	E3	=WORKDAY.INTL(A3-1,C3,"0111111")
G4,I4	G4	=WORKDAY.INTL($A4-1,G2,REPLACE("1111111",MATCH(LEFT($B4,3),DOWS,0),1,0))
E9	E9	=INT((DAY(WORKDAY.INTL(A9-1,C9,"0111111")+6)/7))&LOOKUP(E2,Ord)&" "&TEXT(WORKDAY.INTL(A9-1,C9,"0111111"),"ddd dd-mmm-yyy")

 

[GertFoot]

Thank you for your reply Dave Patton. I am just reading up on how to use formulas to calculate cell values with VBA. I will be back in a jiffy.

 

[aRandomHelper]

Try UDF:

Book1.xlsm
	L	M
11	10-01-22	2nd Monday of 2022
12	27-01-22	4th Thursday of 2022
13	10-02-22	6th Thursday of 2022
14	24-02-22	8th Thursday of 2022
15	10-03-22	10th Thursday of 2022
Sheets

Cell Formulas
Range	Formula
M11:M15	M11	=d(L11)

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

 

[StephenCrump]

I am afraid I am lost. If you would care to take a moment I would be very grateful.

Let's start at the beginning: Excel dates/times are just numbers, e.g. today 23 Feb 2021 is 44,250. I choose to format dates as D MMM YYYY so for me this will display as 23 Feb 2021. Someone in the U.S. might choose mm/dd/yy and the same number will display for them as 02/23/21. But it's the same number - 44250 - that is being stored in the cell.

Because the dates are just numbers, we can do arithmetic with them. This might help show what my formula is doing (given that 1 Jan is exactly 1 week after Christmas Day):

	A	B	C
1	Sat 1 Jan 2022	1	Saturday
2	Sun 2 Jan 2022	1	Sunday
3	Mon 3 Jan 2022	1	Monday
4	Tue 4 Jan 2022	1	Tuesday
5	Wed 5 Jan 2022	1	Wednesday
6	Thu 6 Jan 2022	1	Thursday
7	Fri 7 Jan 2022	1	Friday
8	Sat 8 Jan 2022	2	Saturday
9	Sun 9 Jan 2022	2	Sunday
10	Mon 10 Jan 2022	2	Monday
11	Tue 11 Jan 2022	2	Tuesday
12	Wed 12 Jan 2022	2	Wednesday
13	Thu 13 Jan 2022	2	Thursday
14	Fri 14 Jan 2022	2	Friday
15	Sat 15 Jan 2022	3	Saturday
16	Sun 16 Jan 2022	3	Sunday
17	Mon 17 Jan 2022	3	Monday
18	Tue 18 Jan 2022	3	Tuesday
19	Wed 19 Jan 2022	3	Wednesday
20	Thu 20 Jan 2022	3	Thursday
21	Fri 21 Jan 2022	3	Friday
22	Sat 22 Jan 2022	4	Saturday
23	Sun 23 Jan 2022	4	Sunday
24	Mon 24 Jan 2022	4	Monday
Sheet1

Cell Formulas
Range	Formula
B1:B24	B1	=INT((A1-DATE(YEAR(A1)-1,12,25))/7)
C1:C24	C1	=TEXT(A1,"dddd")

 

[GertFoot]

Thankyou for your reply aRandomHelper. I am overwhelmed by the generous responses on this site.

 

[GertFoot]

Thank you StephenCrump. When I converted the formula to VBA I had to use DateSerial and it all became clearer. By choosing Xmas day you effectively 'reset' all day counts to zero and allowed for any year to start on any of the seven days. Then dividing by 7 gave us the number of times the day had appeared since the beginning of the year.

= Int((DayOfWeek - DateSerial(Year(DayOfWeek) - 1, 12, 25)) / 7)

 

[GertFoot]

aRandomHelper your UDF code will be very helpful. Thank you.