Hello Everyone,
For my company, employees are required to take 10 consecutive days off. I can't seem to get the formula to count consecutive days (including weekend & holidays working). For example, Employee 2 should be 7 days, counting 12/25, 12/26, and 12/27. Here's the formula I tried to use: {=SUMPRODUCT(($A$2:$A$28119=D4),($B$3:$B$28120*($A$3:$A$28120=D4)<>WORKDAY(N(IF({1},$B$2:$B$28119)),1)))}. Is it possible to also identify the start of the consecutive date and end with a formula? Thank you!!
For my company, employees are required to take 10 consecutive days off. I can't seem to get the formula to count consecutive days (including weekend & holidays working). For example, Employee 2 should be 7 days, counting 12/25, 12/26, and 12/27. Here's the formula I tried to use: {=SUMPRODUCT(($A$2:$A$28119=D4),($B$3:$B$28120*($A$3:$A$28120=D4)<>WORKDAY(N(IF({1},$B$2:$B$28119)),1)))}. Is it possible to also identify the start of the consecutive date and end with a formula? Thank you!!
Attachments

10 KB Views: 11