Count of Consecutive Work Days

[skoorBmaS]

Hi Guys, I am trying to get a formula going which will show if an employee has worked over 6 consecutive days. The way in which my data is currently stored is like the following:

DATE	Week	Employee	Hours Worked
01/05/18	18	Bill	10:00
02/05/18	18	Bill	14:00
03/05/18	18	Bill	08:00
01/05/18	18	Phil	10:00
02/05/18	18	Phil	10:00
03/05/18	18	Phil	10:00
04/05/18	18	Phil	10:00
05/05/18	18	Phil	10:00

<tbody>
</tbody>

I would like in another sheet to show if any employee has worked over 6 consecutive days within a week. Please note that the data may not always be inputted in chronological order or with the employees days being sorted together.

Does anyone have a way in which to do this?

Kind Regards

 

[Eric W]

Possibly something like this:

	A	B	C	D	E	F	G
1	DATE	Week	Employee	Hours Worked		Employee	Longest consecutive run
2	5/1/2018	18	Bill	10:00		Bill	4
3	5/2/2018	18	Bill	14:00		Phil	7
4	5/3/2018	18	Bill	8:00
5	5/1/2018	18	Phil	10:00
6	5/2/2018	18	Phil	10:00
7	5/3/2018	18	Phil	10:00
8	5/4/2018	18	Phil	10:00
9	5/5/2018	18	Phil	10:00
10	4-May		bill
11	6-May		phil
12	8-May		bill
13	7-May		phil

<tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula G2 {=MAX(FREQUENCY(IF($C$2:$C$20=F2,IF(ISNUMBER(MATCH($A$2:$A$20,ROW(INDIRECT(MIN($A$2:$A$20)&":"&MAX($A$2:$A$20))),0)),$A$2:$A$20)),IF(ISERROR(MATCH(ROW(INDIRECT(MIN($A$2:$A$20)-1&":"&MAX($A$2:$A$20)+1))&F2,$A$2:$A$20&$C$2:$C$20,0)),ROW(INDIRECT(MIN($A$2:$A$20)-1&":"&MAX($A$2:$A$20)+1)))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This will find the longest streak per person, within the whole range, regardless of week boundaries. If you want to look for a given week only, you could add another cell with an 18 in it, and I could adapt the formula to only consider data with an 18 in column B.

Let us know.

 

[Eric W]

With further reflection, I think the previous formula has some flaws, this should fix them:

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(ROW(INDIRECT(MIN($A$2:$A$20)&":"&MAX($A$2:$A$20)))&F2,$A$2:$A$20&$C$2:$C$20,0)),ROW(INDIRECT(MIN($A$2:$A$20)&":"&MAX($A$2:$A$20)))),IF(ISERROR(MATCH(ROW(INDIRECT(MIN($A$2:$A$20)-1&":"&MAX($A$2:$A$20)+1))&F2,$A$2:$A$20&$C$2:$C$20,0)),ROW(INDIRECT(MIN($A$2:$A$20)-1&":"&MAX($A$2:$A$20)+1)))))

with Control+Shift+Enter.

If you want the version that uses a week number, then:

	A	B	C	D	E	F	G
1	DATE	Week	Employee	Hours Worked	Week	Employee	Longest con secutive run
2	5/1/2018	18	Bill	10:00	19	Bill	1
3	5/2/2018	18	Bill	14:00		Phil	2
4	5/3/2018	18	Bill	8:00
5	5/1/2018	18	Phil	10:00
6	5/2/2018	18	Phil	10:00
7	5/3/2018	18	Phil	10:00
8	5/4/2018	18	Phil	10:00
9	5/5/2018	18	Phil	10:00
10	4-May	18	bill
11	6-May	19	phil
12	8-May	19	bill
13	7-May	19	phil
14

<tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula G2 {=MAX(FREQUENCY(IF(ISNUMBER(MATCH(ROW(INDIRECT(MIN($A$2:$A$20)&":"&MAX($A$2:$A$20)))&F2&$E$2,$A$2:$A$20&$C$2:$C$20&$B$2:$B$20, 0)),ROW(INDIRECT(MIN($A$2:$A$20)&":"&MAX($A$2:$A$20)))),IF(ISERROR(MATCH(ROW(INDIRECT(MIN($A$2:$A$20)-1&":"&MAX($A$2:$A$20)+1))&F2&$E$2,$A$2:$A$20&$C$2:$C$20&$B$2:$B$20,0)),ROW(INDIRECT(MIN($A$2:$A$20)-1&":"&MAX($A$2:$A$20)+1)))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[skoorBmaS]

Hi Eric,

Thank you so much for the above. I have gotten it to work perfectly in the way you showed above. However since then, I have added a few more columns onto my data, in which now Date starts at A3, Week at B3, Name at D3. Then the consecutive run area is now at P3. Within your formula, I have changed the formulas references to reflect those changes, however the formula does not work anymore. Could you please advise or am I missing something simple? The new formula is as below:

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(ROW(INDIRECT(MIN($A$3:$A$5000)&":"&MAX($A$3:$A$5000)))&Q3,$A$3:$A$500&$D$3:$D$5000,0)),ROW(INDIRECT(MIN($A$3:$A$5000)&":"&MAX($A$3:$A$5000)))),IF(ISERROR(MATCH(ROW(INDIRECT(MIN($A$3:$A$5000)-1&":"&MAX($A$2:$A$5000)+1))&Q3,$A$3:$A$5000&$D$3:$D$5000,0)),ROW(INDIRECT(MIN($A$3:$A$5000)-1&":"&MAX($A$3:$A$5000)+1)))))

With further reflection, I think the previous formula has some flaws, this should fix them:

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(ROW(INDIRECT(MIN($A$2:$A$20)&":"&MAX($A$2:$A$20)))&F2,$A$2:$A$20&$C$2:$C$20,0)),ROW(INDIRECT(MIN($A$2:$A$20)&":"&MAX($A$2:$A$20)))),IF(ISERROR(MATCH(ROW(INDIRECT(MIN($A$2:$A$20)-1&":"&MAX($A$2:$A$20)+1))&F2,$A$2:$A$20&$C$2:$C$20,0)),ROW(INDIRECT(MIN($A$2:$A$20)-1&":"&MAX($A$2:$A$20)+1)))))

with Control+Shift+Enter.

 

[skoorBmaS]

Hi Eric,

Please ignore the above. Just as I sent you this, I realised my mistake and now have the formula working. Thank you so much for your help.

Hi Eric,

Thank you so much for the above. I have gotten it to work perfectly in the way you showed above. However since then, I have added a few more columns onto my data, in which now Date starts at A3, Week at B3, Name at D3. Then the consecutive run area is now at P3. Within your formula, I have changed the formulas references to reflect those changes, however the formula does not work anymore. Could you please advise or am I missing something simple? The new formula is as below:

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(ROW(INDIRECT(MIN($A$3:$A$5000)&":"&MAX($A$3:$A$5000)))&Q3,$A$3:$A$500&$D$3:$D$5000,0)),ROW(INDIRECT(MIN($A$3:$A$5000)&":"&MAX($A$3:$A$5000)))),IF(ISERROR(MATCH(ROW(INDIRECT(MIN($A$3:$A$5000)-1&":"&MAX($A$2:$A$5000)+1))&Q3,$A$3:$A$5000&$D$3:$D$5000,0)),ROW(INDIRECT(MIN($A$3:$A$5000)-1&":"&MAX($A$3:$A$5000)+1)))))

 

[Eric W]

Glad I could help.

Good job adapting a complicated formula!