How to determine an end date after a certain number of specific weekdays

[mariantoanet]

Hi everyone, I'm trying to figure out how to create a formula in Google Sheets to determine an end date.

Specifically, I need to calculate a date that occurs after a certain number of specific weekdays. For instance, if the starting date is January 1st, I'd like to find the date that falls on the 12th occurrence of both Tuesdays and Thursdays.

Thank you.

 

[Sergius]

Book1.xlsm
	B	C	D
26	01.01.2024	19.03.2024	Tuesday 12th
27	01.01.2024	21.03.2024	Thursday 12th
Sheet16

Cell Formulas
Range	Formula
C26	C26	=B26-DAY(B26)+1+12*7-WEEKDAY(B26-DAY(B26)+8-3)
C27	C27	=B27-DAY(B27)+1+12*7-WEEKDAY(B27-DAY(B27)+8-5)

 

[mariantoanet]

Thank Sergius for taking the time to help me with this formula. Your suggestion is a great starting point. However, I'm still trying to find a formula that can accommodate a variable start date and also calculate the end date based on a specific count of occurrences of multiple weekdays.

How can I modify the formula to find that date?

Thank you.

 

[Sergius]

In the yellow cells, write what you need: the starting date, the week, the desired day of the week. Based on this data, the formula will calculate the day you need.

Book1.xlsm
	E	F	G	H
23	Start date	01.01.2024
24	Week	12
25	Day of the week	3
26			19.03.2024	Tuesday 12th
Sheet16

Cell Formulas
Range	Formula
G26	G26	=$F$23-DAY($F$23)+1+$F$24*7-WEEKDAY($F$23-DAY($F$23)+8-$F$25)

 

[mariantoanet]

I appreciate you taking the time to help me with this.

I've tried inputting the formula as you suggested, but it seems to be returning an incorrect value. Would you mind double-checking the formula to see if there's anything went wrong?

 

[Sergius]

September 10 falls in the second week of the month. Therefore, the formula produced this date.

 

[Fluff]

Hi & welcome to MrExcel.
Is this what you want.

Fluff.xlsm
	A	B
1
2	Start date	01/01/2024
3	No. of days	12
4		08/02/2024
Sheet6

Cell Formulas
Range	Formula
B4	B4	=WORKDAY.INTL(B2-1,B3,"1010111")

 

[mariantoanet]

Hi & welcome to MrExcel.
Is this what you want.

Fluff.xlsm
	A	B
1
2	Start date	01/01/2024
3	No. of days	12
4		08/02/2024
Sheet6

Cell Formulas
Range	Formula
B4	B4	=WORKDAY.INTL(B2-1,B3,"1010111")

Thank you for the simple yet effective formula. It's exactly what I needed. I save so much time with this

 

[bebo021999]

Using WORKDAY.INTL with day_parameter like this:
forex: "0111111" with Monday
"1101011" with Tuesday and Thusday
(0 indicates specific day of week)

Xep lich cho NV.xlsm
	A	B	C	D
1	start date	01-Jan-24	01-Jan-24	01-Jan-24
2	occurrence	12	12	20
3	day of week	Tue	Tue, Thu	Fri
4	end date	19-Mar-24	08-Feb-24	17-May-24
Sheet3

Cell Formulas
Range	Formula
B4	B4	=WORKDAY.INTL(B1,B2,"1011111")
C4	C4	=WORKDAY.INTL(C1,C2,"1010111")
D4	D4	=WORKDAY.INTL(D1,D2,"1111011")

 

[Fluff]

Thank you for the simple yet effective formula. It's exactly what I needed. I save so much time with this

You're welcome & thanks for the feedback.