• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • Consecutive Leave.xlsx
    10 KB · Views: 11
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

  • Consecutive Leave.xlsx
    12.7 KB · Views: 10
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

  • Chandoo45353Consecutive Leave.xlsx
    22 KB · Views: 7
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

  • Consecutive Leave v.2.xlsx
    12.7 KB · Views: 3
If you cut and paste the additional data to directly below the table in columns A:B in my offering Chandoo45353Consecutive Leave.xlsx in msg#3, then right-click the table at D1 and choose Refresh, you should have your new data:
72267
 
Please try at
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

  • Consecutive Leave.xlsx
    12.7 KB · Views: 4
Back
Top