Calculate no. of workdays excluding holidays & employee vacations

[lester777]

Hello everyone.

I have been asked to create a simple tracker in our office which will let us determine the estimated finish date of a task assigned to the employees.

I am working on a workbook that has 3 worksheets similar to what is shown below:


Sheet 1:
********

Employee ID	Days	Start date	Finish Date
11111	7	10/31/2018	????
22222	3	10/10/2018	????
33333	7	10/31/2018	????

Sheet 2:
********

Date	Holiday
11/01/2018	All Saints Day
12/25/2018	Christmas
01/01/2019	New Year

Sheet 3:
********

Employee ID	Date of Vacation
11111	11/03/2018
11111	11/04/2018
22222	10/11/2018
33333	11/03/2018

Basically, I need to determine the value of the Finish Date column in Sheet 1, but I also need to exclude the holidays in Sheet 2 and the vacation dates taken by the employees which is in sheet 3.

I know I can use the WORKDAY(start_date, days, [holidays]) function for this, but I am having trouble combining the holiday and vacation lists to be used as exclusion.

I'd like to ask for some advice on how to accomplish this.

[63falcondude]

Hello and welcome to the forum.

Try this in Sheet1!D2:

=WORKDAY(WORKDAY(C3,B3,Sheet2!A$2:A$4),COUNTIFS(Sheet3!A:A,A3,Sheet3!B:B,">="&C3,Sheet3!B:B,"<="&WORKDAY(C3,B3,Sheet2!A$2:A$4)))

[Bo_Ry]

Please check attached file


D2 =WORKDAY(C2,B2,IFERROR(CHOOSE({1,2},$F$2:$F$4,OFFSET($J$1,MATCH(A2,$I$2:$I$5,),,COUNTIF($I$2:$I$5,A2))),""))

[lester777]

Wow, that was fast
Both solutions given are working. I don't quite understand how they work yet, but I'll try to do some more reading in order to understand them.
Thank you 63falcondude and Bo_Ry.

[63falcondude]

You're welcome. Thanks for the rep!

The formula from post #2 starts with a normal WORKDAY function that only uses the company-wide holidays.

The COUNTIFS function counts the number of rows where that employee is found in the sheet with the vacation days (where those vacation days lie between the start and end dates).

The outermost WORKDAY function adds the number of days from the COUNTIFS function to the original WORKDAY function thus including both vacation days and holidays.

[Bo_Ry]

IFERROR(CHOOSE({1,2},$F$2:$F$4,OFFSET($J$1,MATCH(A2,$I$2:$I$5,),,COUNTIF($I$2:$I$5,A2))),"")
This is for combine holiday and vacation together

Choose ({1,x}, $F$2:$F$4,xx) is holiday
Chose ({x,2}, xx,OFFSET($J$1,MATCH(A2,$I$2:$I$5,),,COUNTIF($I$2:$I$5,A2)) is for vacation for each ID.

IFERROR is needed if holiday and vacation is not same dimension, the exceed one will give #N/A! and iferror will change error to ""