I need to calculate the number of holidays, falling on working days only, between two dates

0
votes

I have a table, called Holidays, which lists all the dates classed as a holiday:

Date
01/01/2020
10/04/2020
13/04/2020

I have a table called employees, which shows an employee's working days, in a 7 digit syntax, where 0 is a working day and 1 is not a working day. It goes from Monday to Sunday. So '0000011' would mean that person works on Monday to Friday, and does not work on Saturday/Sunday. 

Code     WorkingDays
A1       0000011
B1       1000111

I have a table of date ranges as such:

From         To          Employee
01/01/2020   31/05/2020  A1
01/01/2020   31/05/2020  B1

What I would like to return is:

From         To          Employee    Holidays
01/01/2020   31/05/2020  A1          3
01/01/2020   31/05/2020  B1          0

Employee A1 - there are 3 Holidays which fall between those two dates and are also working days Employee B1 - there are no Holidays which fall between those two dates and are also working days

I need something similar to the NETWORKDAYS.INTL function, but instead of calculating working days I need it to calculate holidays somehow. Is this possible?

2
excelexcel-formula
Find the total days then subtract weekend days the compare with number of days worked. – Solar Mike

2 Answers

3
votes

Use NETWORKDAYS.INTL twice, one with just the weekends and the other that include the holidays and subtract the later from the prior:

=NETWORKDAYS.INTL(D2,E2,C2)-NETWORKDAYS.INTL(D2,E2,C2,$A$2:$A$4)

enter image description here

1
votes

I'd think maybe SUMPRODUCT is a good alternative to what you are looking for:

enter image description here

Formula in G2:

=SUMPRODUCT((A$2:A$4>=D2)*(A$2:A$4<=E2)*(MID(C2,WEEKDAY(A$2:A$4,2),1)="0"))

Drag down.