Consecutive days count with multiple staff

Hi all

I'm sure this is straight forward but my limited brain power is....well....limited. How can I get Excel to calculate the maximum number of consecutive days per employee, i.e., Person 2 = '2' as there are 4 periods when the consecutive days do not go past 2 days.
A B C
Name Actual Start Date Actual End Date
Person 1 01/08/2019 01/08/2019
Person 2 01/08/2019 01/08/2019
Person 1 01/08/2019 01/08/2019
Person 1 01/08/2019 01/08/2019
Person 2 05/08/2019 05/08/2019
Person 2 07/08/2019 07/08/2019
Person 1 08/08/2019 08/08/2019
Person 2 08/08/2019 08/08/2019
Person 1 08/08/2019 08/08/2019
Person 1 08/08/2019 08/08/2019
Person 2 14/08/2019 14/08/2019
Person 1 15/08/2019 15/08/2019
Person 2 15/08/2019 15/08/2019
Person 2 19/08/2019 19/08/2019
Person 2 21/08/2019 21/08/2019
Person 1 22/08/2019 22/08/2019
Person 2 22/08/2019 22/08/2019
Person 1 22/08/2019 22/08/2019
Person 1 22/08/2019 22/08/2019
Person 2 28/08/2019 28/08/2019
Person 1 29/08/2019 29/08/2019
Person 2 29/08/2019 29/08/2019
Person 1 29/08/2019 29/08/2019
Person 1 29/08/2019 29/08/2019
Person 1 29/08/2019 29/08/2019
etc...

Hi Richard

The answer I would be looking for person 2 would be '2' as they have not worked more than 2 consecutive days in August.

Thanks

Stuart

Sorry but this formula only results in an answer of 0 for all rows

[QUOTE=Richard Buttrey;5194810]With a helper column D and in D2 copied down

Formula:

Please Login or Register  to view this content.

Thankyou Richard and my apologies for not attaching workbook

Your formula is counting periods whereas I am looking for the highest amount of consecutive days worked, i.e., all 4 periods you mention are no more than 2 days each time, therefore the highest consecutive days would be 2. If it went from 7/8/18 - 9/8/19 the answer would be 3.

Stuart

Originally Posted by Richard Buttrey

I'ma attaching a workbook containing your data. Please note you should always supply these rather than just listing information as text.

I count 4 consecutive dayas using the formula I gave you.

End on 7/8/2019 Start on 8/8/2019
End on 14/8/2019 Start on 15/8/2019
End on 21/8/2019 Start on 22/8/2019
End on 28/8/2019 Start on 29/8/2019

Please explain how you are counting just 2. Your OP mentioned 4.

Richard I think it is slightly unclear, but it is the maximum run of consecutive days in the period. So whilst consecutive days occur on 4 occasions which you have identified, the thread is wanting 2 as an answer, all be it that it occurred on 4 occasions!

If the list could be sorted by employee and then date it is an easy task with a helper column
in d3 =IF(B3=B2,D2,IF(B3=B2+1,D2+1,1))

then you want the maximum of this when the person is the person of interest

as an array
=MAX(IF(A:A=G2,D:D,""))

Although I assume that there are no weekends in the formula so Friday Monday is consecutive

Thanks Dave

There are weekends to be included. How would this change the formula?

Stuart

You really need to give data displaying your scenarios and the result, as highlighted in the previous post
=IF(B4=B3,D3,IF(OR(B4=B3+1, AND(B4=B3+3, WEEKDAY(B3)=6)),D3+1,1))

would account for weekends perhaps