counting consecutive days worked.....

[chris_sweezy]

Trying to count employee's consecutive days worked (no more than 13, 14th has to be a rest day). Problem is.. count should restart after an absence.

	8/1	8/2	8/3	8/4	8/6	8/7	8/8
Consecutive Days Worked	In	In	In	In	In	In	In
	1	1	1	0	1	1	1
	1	1	0	1	0	1	1
	0	0	0	0	0	0	0
	1	1	1	1	1	1	1
	1	1	0	0	1	1	1

 

[shg]

This is dead easy using a formula if the data is arranged down a single column, and much harder in a table.

You could select the range of interest and run this (perhaps from a shortcut key):

Sub TooManyDays()
  Dim r             As Range
  Dim cell          As Range
  Dim n             As Long

  Set r = ActiveWindow.RangeSelection
  r.Interior.Color = vbWhite

  For Each cell In r
    Select Case True
      Case IsEmpty(cell.Value)
        cell.Interior.Color = vbYellow
      Case cell.Value = 1
        n = n + 1
        If n > 13 Then cell.Interior.Color = vbRed
      Case cell.Value = 0
        n = 0
      Case Else
        cell.Interior.Color = vbYellow
    End Select
  Next cell
End Sub

For example,

	A​	B​	C​	D​	E​	F​	G​
1​	In​	In​	In​	In​	In​	In​	In​
2​	1​	1​	1​	1​	1​	1​	1​
3​	1​	1​	1​	1​	1​	1​	1​
4​	0​	1​	1​	1​	1​	1​	1​
5​	0​	1​	1​	1​	1​	1​	1​
6​	1​	0​	0​	1​	1​	1​	1​
7​	1​	1​	1​	1​	1​	1​	1​
8​	1​	0​	1​	1​	1​	1​	1​
9​	1​	1​	1​	1​	1​	1​	1​
10​	1​	1​	1​	1​	1​	1​	0​
11​	1​	1​	1​	1​	1​	0​	1​
12​	1​	0​	1​	1​	1​	1​	1​
13​	1​	0​	0​	1​	1​	1​	1​
14​	1​	0​	1​	1​	1​	1​	1​
15​	1​	1​	1​	0​	1​	1​	1​
16​	1​	1​	1​	1​	1​	1​	1​
17​	1​	1​	1​	0​	1​	1​	0​
18​	1​	1​	1​	1​	1​	1​	1​
19​	1​	1​	1​	1​	1​	1​	1​
20​	1​	1​	0​	1​	1​	1​	1​
21​	1​	1​	1​	0​	0​	1​	1​
22​	1​	1​	1​	1​	1​	1​	1​
23​	1​	1​	1​	0​	1​	1​	1​

 

[chris_sweezy]

shg, thanks for your help. Do I insert your code in the consecutive days worked cell and drag down?

 

[apgmin]

this is a VBA code and you need to use alt f11
Insert module

 

[chris_sweezy]

Here is a better look at my data... I should have sent better look initially. Might help?

					8/1	8/2	8/3	8/4	8/6	8/7	8/8	8/9	8/10	8/11	8/12	8/13
EMPLOYE E NUMBER	EMPLOYEE NAME	CLASS	RATE	Consecutive Days Worked	In	In	In	In	In	In	In	In	In	In	In	In
APA6128	ALL, DAVID "RANDY"	GF	#		1	1	1	0	1	1	1	1	1	1	1	1
APA0430	ALVARADO, JOSE ANGEL	Lead	#		1	1	0	1	0	1	1	1	1	1	1	1
APA8155	ARDOIN, BRANDON	Lead	#		0	0	0	0	0	0	0	0	0	0	0	0
APA0057	ARRIAGA MORALES, JUAN	Carpenter	#		1	1	1	1	1	1	1	1	1	1	1	1
APA1964	BARULICH, NICHOLAS	GF	#		1	1	0	0	1	1	1	1	1	1	1	1
APA9168	BOLDEN, KENNETH	Lead	#		1	1	1	1	1	1	1	1	1	1	1	1

<colgroup><col width="58" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2432;"> <col width="34" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1344;"> <col width="15" style="width: 11pt; mso-width-source: userset; mso-width-alt: 608;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2752;"> <col width="30" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1184;" span="12"> <tbody> </tbody>

 

[chris_sweezy]

Here is a better look at my data... I should have sent better look initially. Might help?

					8/1	8/2	8/3	8/4	8/6	8/7	8/8	8/9	8/10	8/11	8/12	8/13
EMPLOYE E NUMBER	EMPLOYEE NAME	CLASS	RATE	Consecutive Days Worked	In	In	In	In	In	In	In	In	In	In	In	In
APA6128	ALL, DAVID "RANDY"	GF	#		1	1	1	0	1	1	1	1	1	1	1	1
APA0430	ALVARADO, JOSE ANGEL	Lead	#		1	1	0	1	0	1	1	1	1	1	1	1
APA8155	ARDOIN, BRANDON	Lead	#		0	0	0	0	0	0	0	0	0	0	0	0
APA0057	ARRIAGA MORALES, JUAN	Carpenter	#		1	1	1	1	1	1	1	1	1	1	1	1
APA1964	BARULICH, NICHOLAS	GF	#		1	1	0	0	1	1	1	1	1	1	1	1
APA9168	BOLDEN, KENNETH	Lead	#		1	1	1	1	1	1	1	1	1	1	1	1

<tbody>
</tbody>

I apologize, I'm new at this.... This counting of consecutive days worked will be from whatever the current date is, looking back. Also, each column header is a date... above the "In".
Thanks for your patience and help.

 

[chris_sweezy]

Can anyone please have a look? I've searched and searched, found a couple that ALMOST accomplishes what's needed

 

[shg]

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

1​

#​

Name​

Class​

Rate​

Cons​

In​

In​

In​

In​

In​

In​

In​

In​

In​

In​

In​

In​

2​

APA6128

Alan

GF

#

8​

1​

1​

1​

0​

1​

1​

1​

1​

1​

1​

1​

1​

3​

APA0430

Barb

Lead

#

7​

1​

1​

0​

1​

0​

1​

1​

1​

1​

1​

1​

1​

4​

APA8155

Cain

Lead

#

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

5​

APA0057

Dana

Carpenter

#

12​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

6​

APA1964

Eric

GF

#

8​

1​

1​

0​

0​

1​

1​

1​

1​

1​

1​

1​

1​

7​

APA9168

Fran

Lead

#

12​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

In E2, confirmed with Ctrl+Shift+Enter,

=MAX(FREQUENCY(IF(F2:Q2=1, COLUMN(F2:Q2)), IF(F2:Q2<>1, COLUMN(F2:Q2))))

 

[chris_sweezy]

SHG , thanks so much for taking your time to help me. I am really appreciative. I copy & paste, hit Ctrl+Shift+Enter, but it doesn't work. Would it be possible making it count the 13 days from present date? It will change everyday.... Again, thanks.

 

[shg]

Paste the formula in the formula bar, press and hold the Control and Shift keys, then press Enter.