# Consecutive Days Off Assistance

#### dliu

##### New Member
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!!

#### Attachments

• 10 KB Views: 11

#### Excel Wizard

##### Member
Please put the Holiday list in I2:Ixx.

E2
=G2-F2+1

F2
=IFERROR(AGGREGATE(15,6,\$B\$2:\$B\$61/(\$A\$2:\$A\$61=D2)/(WORKDAY(+\$B\$2:\$B\$61,1,\$I\$2:\$I\$13)=--(\$B\$3:\$B\$62)),1),--VLOOKUP(D2,\$A\$2:\$B\$61,2,0))

G2
=IFERROR(AGGREGATE(14,6,\$B\$3:\$B\$62/(\$A\$2:\$A\$61=D2)/(WORKDAY(+\$B\$2:\$B\$61,1,\$I\$2:\$I\$13)=--(\$B\$3:\$B\$62)),1),F2)

#### Attachments

• 12.7 KB Views: 10
• Thomas Kuriakose and herofox

#### p45cal

##### Well-Known Member
Table at cell D1 of the attached. Right-click in it and choose Refresh to update.
Holidays to be added to table at J1
My answers are a little different from Excel Wizard's

#### Attachments

• 22 KB Views: 7
• Thomas Kuriakose

#### dliu

##### New Member
Please put the Holiday list in I2:Ixx.

E2
=G2-F2+1

F2
=IFERROR(AGGREGATE(15,6,\$B\$2:\$B\$61/(\$A\$2:\$A\$61=D2)/(WORKDAY(+\$B\$2:\$B\$61,1,\$I\$2:\$I\$13)=--(\$B\$3:\$B\$62)),1),--VLOOKUP(D2,\$A\$2:\$B\$61,2,0))

G2
=IFERROR(AGGREGATE(14,6,\$B\$3:\$B\$62/(\$A\$2:\$A\$61=D2)/(WORKDAY(+\$B\$2:\$B\$61,1,\$I\$2:\$I\$13)=--(\$B\$3:\$B\$62)),1),F2)
Thank you for this!

I'm having issues adding additional data. As you can see, Employee 18 end date should've been December 14 for the consecutive. Am I doing something incorrectly?

#### Attachments

• 12.7 KB Views: 3

#### Excel Wizard

##### Member
F2
=WORKDAY(G2,-MAX(FREQUENCY(IF((\$A\$2:\$A\$161=D2)*(WORKDAY(+\$B\$2:\$B\$161,1,\$I\$2:\$I\$13)=--(\$B\$3:\$B\$162)),ROW(\$B\$2:\$B\$161)),IF((\$A\$2:\$A\$161=D2)*(WORKDAY(+\$B\$2:\$B\$161,1,\$I\$2:\$I\$13)<>--(\$B\$3:\$B\$162)),ROW(\$B\$2:\$B\$161)))),\$I\$2:\$I\$13)

G2
=INDEX(--\$B\$1:\$B\$161,LOOKUP(2,1/FREQUENCY(0,1/(FREQUENCY(IF((\$A\$2:\$A\$161=D2)*(WORKDAY(+\$B\$2:\$B\$161,1,\$I\$2:\$I\$13)=--(\$B\$3:\$B\$162)),ROW(\$B\$2:\$B\$161)),IF((\$A\$2:\$A\$161=D2)*(WORKDAY(+\$B\$2:\$B\$161,1,\$I\$2:\$I\$13)<>--(\$B\$3:\$B\$162)),ROW(\$B\$2:\$B\$161)))+1)),SMALL(IF((\$A\$2:\$A\$161=D2)*(WORKDAY(+\$B\$2:\$B\$161,1,\$I\$2:\$I\$13)<>--(\$B\$3:\$B\$162)),ROW(\$B\$2:\$B\$161)),ROW(\$B\$1:\$B\$161))))

#### Attachments

• 12.7 KB Views: 3
• herofox