Calculate Work days

[Svmaxcel]

We make scheduled for agents and they get 2 weekly offs(any day) as per wordload.
File is attached for example.

Employee John has week off on 6 and 7 Aug, then next week its 16 and 17 Aug.
I want to know how many days the employee has to work to get his next week off.
John will come to work on 8 Aug till 15 Aug so answer is 8 days.

 

[bosco_yip]

Try,

In I6, Ctrl+Shift+Enter copied down to I8

=6+MATCH(1=1,J6: P6="OFF",0)-MATCH(2,1/(B6:H6="OFF"))

and, select I6:I8 >> copy/paste to Q6:Q8

Regards
Bosco

 

[Svmaxcel]

Request you to explain the formula