Calculate future dates

[jgalas]

Hi! I have a task that takes 50 hours. it can start on any business day and runs 2 hours on Tuesdays and 2 hours on Wednesdays. I want to know the end date of the task.
Ex: if the start date is 03/22/2023, the end date will be 06/20/2023 excluding a holiday on Tuesday (holidays and vacation days can be in a column)
can someone give me a tip please

 

[Fluff]

You're welcome & thanks for the feedback.

 

[awoohaw]

i come up with a different end day:

Book1
	A	B	C	D	E	F	G	H
1		Start	Hours	Max Hrs Per Day
2		2023-03-22	50	2	2023-06-21			Holidays
3								Tuesday, June 6, 2023
Sheet1

Cell Formulas
Range	Formula
E2	E2	=WORKDAY.INTL(B2,C2/2,"1001111",$H$3)
H3	H3	=DATE(2023,6,6)

this is a corrected version.

 

[Fluff]

How about

=WORKDAY.INTL(A2-1,24,"1001111",D2:D7)

Where A2 is the start date & D2:D7 are holidays.

 

[jgalas]

i come up with a different end day:

Book1
	B	C	D	E	F	G	H
1	Start	Hours	Max Hrs Per Day
2	2023-03-22	60	2	2023-07-05			Holidays
3							Tuesday, June 6, 2023
Sheet1

Cell Formulas
Range	Formula
E2	E2	=WORKDAY.INTL(B2,C2/2,"1100111",$H$3)
H3	H3	=DATE(2023,6,6)

Thanks for responding.
Your end date is different because you used 60 hours instead of 50.
If you have several holidays in the same period, how to do it?

 

[awoohaw]

i come up with a different end day:

Book1
	A	B	C	D	E	F	G	H
1		Start	Hours	Max Hrs Per Day
2		2023-03-22	50	2	2023-06-21			Holidays
3								Tuesday, June 6, 2023
Sheet1

Cell Formulas
Range	Formula
E2	E2	=WORKDAY.INTL(B2,C2/2,"1001111",$H$3)
H3	H3	=DATE(2023,6,6)

this is a corrected version.

I guess my number is incorrect becuase I add all the hours, I'm not sure what day of the week the start of the project is.

 

[jgalas]

How about

=WORKDAY.INTL(A2-1,24,"1001111",D2:D7)

Where A2 is the start date & D2:D7 are holidays.

Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[awoohaw]

Here is with different "assignment dates" if you want to call it that. Holidays go in the range under "Holidays" ... just put that range reference in argument 4.

Book1
	B	C	D	E	F	G	H
1	Start	Hours	Max Hrs Per Day
2	Monday, March 20, 2023	50	2	Wednesday, June 14, 2023			Holidays
3	Tuesday, March 21, 2023	50	2	Wednesday, June 14, 2023			Tuesday, June 6, 2023
4	Wednesday, March 22, 2023	50	2	Tuesday, June 20, 2023
5	Thursday, March 23, 2023	50	2	Wednesday, June 21, 2023
6	Friday, March 24, 2023	50	2	Wednesday, June 21, 2023
7	Saturday, March 25, 2023	50	2	Wednesday, June 21, 2023
8	Sunday, March 26, 2023	50	2	Wednesday, June 21, 2023
9	Monday, March 27, 2023	50	2	Wednesday, June 21, 2023
10	Tuesday, March 28, 2023	50	2	Wednesday, June 21, 2023
11	Wednesday, March 29, 2023	50	2	Tuesday, June 27, 2023
Sheet1

Cell Formulas
Range	Formula
H3	H3	=DATE(2023,6,6)
B7:B11	B7	=B6+1
E2:E11	E2	=WORKDAY.INTL(B2-1,C2/2,"1001111",$H$3)

 

[Fluff]

Just realised my formula is wrong, it should be

=WORKDAY.INTL(A2-1,25,"1001111",D2:D7)