Count Individual Occurences that are consecutive for the days of the week

[Needtoknow]

Hi All,
I'm looking for a formula to Count Individual Occurrences that are consecutive for the days of the week

I have a list of names on one tab and I want to look at another tab and do the following:
1.) Count individual occurrences that are consecutive for the days of the week
2.) a new occurrence would be if a day was skipped
3.) do not include holidays and weekends

The data on the first tab is only names:
John Doe
Jane Doe
Billy Doe

The data on the second tab is:
Date EmpNo Employee Name Pay Code Yes/No
17/08/18 123 John Doe Yes
17/08/25 123 John Doe Yes
17/12/21 123 John Doe Yes
17/02/06 123 John Doe Yes

Is there an easy way to do this?
Thanks,
Jim

 

[magpie2000k]

Im not sure i understand

Where are your consecutive days. In Date colum on sheet 2
in your example none are consecutive.

Where do you want the output to go?

Are you looking to count

17/01/18 123 John doe
17/01/19 123 John doe
17/01/20 123 John doe
17/01/18 123 Jane doe
17/01/06 123 John doe
17/01/18 123 billy Doe

ie in that example John doe worked 3 consecutive days?

 

[Needtoknow]

Yes, exactly. I'd like my data to go on the first tab (but it's looking at the second tab data)

 

[Needtoknow]

I forgot to mention that I only want the consecutive counts to work if the Pay Code (column) is "Yes"
Also, I need to not include weekends and holidays...

 

[magpie2000k]

Take a look at this

This is how I would do it but im not very good at explaining on here.

https://www.mrexcel.com/forum/excel-questions/534050-calculate-number-consecutive-dates.html

Post 9 should be what you are looking for with some adaptation

simply change the countif to countifs and add the Yes criteria..


make sense

 

[Needtoknow]

Yes, this seems to be the exact same problem but I can't get Excel to work these formulas....

=SUMPRODUCT(($A$2:$A$203529=S2)*($B$3:$B$203529-$B$2:$B$203528=1))+(COUNTIF($A$2:$A$203529,S2)>0)

{=MAX(FREQUENCY(IF($A$2:$A$203529=$S2,IF($B$3:$B$203529-$B$2:$B$203528=1,ROW($B$2:$B$203528))),IF(($A$2:$A$203529<>$S2)+($B$3:$B$203529-$B$2:$B$203528<>1),ROW($B$2:$B$203528))))+(COUNTIF($A$2:$A$203529,$S2)>0)}

 

[Needtoknow]

Even when I try to recreate the example that thread, I can't seem to make it work.....weird.
I even copied the data over

 

[Needtoknow]

id date id consecutive
7 04/01/2010 7 #VALUE !
7 15/01/2010
7 25/01/2010
7 26/01/2010
7 19/02/2010
7 03/03/2010
7 09/03/2010
7 19/03/2010
7 16/04/2010
7 13/05/2010
7 22/06/2010
7 23/06/2010
7 10/07/2010
7 16/07/2010
7 26/07/2010
7 07/08/2010
7 12/08/2010
7 21/08/2010
7 04/09/2010
7 07/09/2010
7 08/09/2010
7 09/09/2010
7 05/10/2010
7 21/10/2010
7 05/11/2010
7 13/11/2010
10 14/11/2010




=SUMPRODUCT(($A$2:$A$28=$D2)*($B$3:$B$29-$B$2:$B$28=1))+(COUNTIF($A$2:$A$28,$D2)>0)

 

[Needtoknow]

I got it working finally.
The date was text. When I changed it to a date it worked.
Thx for your help