Calculating future date excluding Public Holiday, Weekends and Backdate specific date

[rajatqa]

Hi. Im trying to calculate TAT from today, excluding public holiday, weekends and specific date.

Assume today's date - 18/06/2024
For example 60 days from today - 08/07/2024
However for that date, employee has an approved leave and unable to attend the task on that date
And we cant extend the date.

Currently im using =WORKDAY.INTL(E9, 60, H9:H22)
whereas the E9 is for todays date
60 for 60 days TAT
H9:H22 are for date listed for Public Holiday

I need the TAT to fall on weekdays thus instead of 08/07/2024 where the employee is on leave, the future date become 05/07/2024 - not 06,07/07/2024 during weekend or 09/07/2024 as we cant extend the due date.

Appreciate your help in this.

Thank you.

 

[etaf]

it seems to be working for me
I changed to 4 days for simplicity
I used 4 days from today - 11/4/24
and put 15th and 18th as holiday
and it gives 19th

BUT i did specify the weekend
=WORKDAY.INTL(E9, 4,1,H9:H22)

so changed you formula to include a weekend type 1 = Sat & Sun

Currently im using =WORKDAY.INTL(E9, 60, H9:H22)

the 1 here
=WORKDAY.INTL(E9, 4,1,H9:H22)

i may not be understanding the issue

Book1
	A	B	C	D	E	F	G	H
1		4/19/24
2
3
4
5
6
7
8
9					4/11/24			4/18/24
10								4/15/24
11					4/15/24
12
Sheet1

Cell Formulas
Range	Formula
B1	B1	=WORKDAY.INTL(E9, 4,1,H9:H22)
E11	E11	=E9+4