Annual Event Calculation

[kyddrivers]

I am building a list of events that occur on set weekends each year and trying to calculate the dates of each event moving forward.

This is the base formula I am using (where E2 is the Year) and adjusting the weeks and months as needed:
=DATE($E$2,6,1)+CHOOSE(WEEKDAY(DATE($E$2,6,1)),6,5,4,3,2,1,0)

This is working until mid-June 2024. Please help me understand why June 15 and 16 are not playing nice and what I need to do to make it work for 2024 and forward.

Thanks in advance!

Event Dates.xlsx
	M	N	O
48
49	Event Date	Event Date Calc	DOW
50	1-Jun	6/1/2024	Saturday
51	1-Jun	6/1/2024	Saturday
52	2-Jun	6/2/2024	Sunday
53	2-Jun	6/2/2024	Sunday
54	14-Jun	6/14/2024	Friday
55	14-Jun	6/14/2024	Friday
56	15-Jun	6/8/2024	Saturday
57	15-Jun	6/8/2024	Saturday
58	16-Jun	6/9/2024	Sunday
59	16-Jun	6/9/2024	Sunday
2024

Cell Formulas
Range	Formula
N50:N51	N50	=DATE($E$2,6,1)+CHOOSE(WEEKDAY(DATE($E$2,6,1)),6,5,4,3,2,1,0)
O50:O59	O50	=WEEKDAY(N50)
N52:N53	N52	=DATE($E$2,6,1)+CHOOSE(WEEKDAY(DATE($E$2,6,1)),0,6,5,4,3,2,1)
N54:N55	N54	=DATE($E$2,6,1)+7+CHOOSE(WEEKDAY(DATE($E$2,6,1)),5,4,3,2,1,0,6)
N56:N57	N56	=DATE($E$2,6,1)+7+CHOOSE(WEEKDAY(DATE($E$2,6,1)),6,5,4,3,2,1,0)
N58:N59	N58	=DATE($E$2,6,1)+7+CHOOSE(WEEKDAY(DATE($E$2,6,1)),0,6,5,4,3,2,1)

 

[StephenCrump]

I am building a list of events that occur on set weekends each year and trying to calculate the dates of each event moving forward.

It is not all clear how your various formulae relate to the question.

Can you describe in words what you're trying to do?

Based on your description, I assume this means something like: Find the first weekend in June in any year, where first weekend means the Saturday of that weekend is the first Saturday of the month?

 

[Phuoc]

Maybe like this:

Book1
	M	N	O
49	Event Date	Event Date Calc	DOW
50	1/6/2024	1/6/2024	7
51	1/6/2024	1/6/2024	7
52	2/6/2024	2/6/2024	1
53	2/6/2024	2/6/2024	1
54	14/6/2024	15/6/2024	7
55	14/6/2024	15/6/2024	7
56	15/6/2024	15/6/2024	7
57	15/6/2024	15/6/2024	7
58	16/6/2024	16/6/2024	1
59	16/6/2024	16/6/2024	1
Sheet1

Cell Formulas
Range	Formula
N50:N59	N50	=WORKDAY.INTL(M50-1,1,"1111100")
O50:O59	O50	=WEEKDAY(N50)

 

[kyddrivers]

It is not all clear how your various formulae relate to the question.

Can you describe in words what you're trying to do?

Based on your description, I assume this means something like: Find the first weekend in June in any year, where first weekend means the Saturday of that weekend is the first Saturday of the month?

Take 2:
I have a list of events that take place on a set weekend each year. The actual dates they take place float from year to year and are not static, like the 4th of July.

Below are some of the events and their dates in 2024. I borrowed the formula for calculating annual holiday dates and figured I could use it to calculate each year's event dates.

Event Dates.xlsx
	D
4	When
5	2-Mar
6	2-Mar
7	3-Mar
8	3-Mar
9	23-Mar
10	23-Mar
11	24-Mar
12	24-Mar
13	6-Apr
14	6-Apr
15	7-Apr
16	7-Apr
17	12-Apr
18	12-Apr
19	13-Apr
20	13-Apr
21	14-Apr
22	14-Apr
23	19-Apr
24	19-Apr
25	20-Apr
26	20-Apr
27	21-Apr
28	21-Apr
29	27-Apr
30	27-Apr
31	28-Apr
32	28-Apr
33	3-May
34	3-May
35	4-May
36	4-May
37	5-May
38	5-May
39	10-May
40	10-May
41	11-May
42	11-May
43	12-May
44	12-May
45	25-May
46	25-May
47	26-May
48	26-May
49	27-May
50	27-May
51	30-May
52	30-May
53	31-May
54	31-May
55	1-Jun
56	1-Jun
57	2-Jun
58	2-Jun
59	14-Jun
60	14-Jun
61	15-Jun
62	15-Jun
63	16-Jun
64	16-Jun
2024

I hope this better explains what I am trying to accomplish.

Thanks!

 

[kyddrivers]

Take 2:
I have a list of events that take place on a set weekend each year. The actual dates they take place float from year to year and are not static, like the 4th of July.

Below are some of the events and their dates in 2024. I borrowed the formula for calculating annual holiday dates and figured I could use it to calculate each year's event dates.

Event Dates.xlsx
	D
4	When
5	2-Mar
6	2-Mar
7	3-Mar
8	3-Mar
9	23-Mar
10	23-Mar
11	24-Mar
12	24-Mar
13	6-Apr
14	6-Apr
15	7-Apr
16	7-Apr
17	12-Apr
18	12-Apr
19	13-Apr
20	13-Apr
21	14-Apr
22	14-Apr
23	19-Apr
24	19-Apr
25	20-Apr
26	20-Apr
27	21-Apr
28	21-Apr
29	27-Apr
30	27-Apr
31	28-Apr
32	28-Apr
33	3-May
34	3-May
35	4-May
36	4-May
37	5-May
38	5-May
39	10-May
40	10-May
41	11-May
42	11-May
43	12-May
44	12-May
45	25-May
46	25-May
47	26-May
48	26-May
49	27-May
50	27-May
51	30-May
52	30-May
53	31-May
54	31-May
55	1-Jun
56	1-Jun
57	2-Jun
58	2-Jun
59	14-Jun
60	14-Jun
61	15-Jun
62	15-Jun
63	16-Jun
64	16-Jun
2024

I hope this better explains what I am trying to accomplish.

Thanks!

For the first 4 events on the list, using the =DATE($E$2,3,1)+CHOOSE(WEEKDAY(DATE($E$2,3,1)),6,5,4,3,2,1,0)

I can calculate that their dates in 2025 will be March 1 & 2.

Event Dates.xlsx
	E
2	2025
3
4	Date
5	3/1/2025
6	3/1/2025
7	3/2/2025
8	3/2/2025
2025

Cell Formulas
Range	Formula
E5:E6	E5	=DATE($E$2,3,1)+CHOOSE(WEEKDAY(DATE($E$2,3,1)),6,5,4,3,2,1,0)
E7:E8	E7	=DATE($E$2,3,1)+CHOOSE(WEEKDAY(DATE($E$2,3,1)),0,6,5,4,3,2,1)

I want to be able to do this for all of the event dates annually for 2025, 2026, 2027 etc.

 

[StephenCrump]

Sorry for the late reply. I didn't get a message when you posted back.

Here's a general solution to determining the Nth occurrence of a particular day in a particular year/month.

	A	B	C	D	E	F	G	H
1	Year	Month	Day	Occurrence	Required date		Days
2	2025	3	Sat	1	Sat 1 Mar 2025		Sun	1
3							Mon	2
4							Tue	3
5							Wed	4
6							Thu	5
7							Fri	6
8							Sat	7
9
Sheet1

Cell Formulas
Range	Formula
E2	E2	=LET(d,DATE(A2,B2,7*D2+1)-WEEKDAY(DATE(A2,B2,8-VLOOKUP(C2,Days,2,))),IF(MONTH(d)<>B2,"n/a",d))

Named Ranges
Name	Refers To	Cells
Days	=Sheet1!$G$2:$H$8	E2