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,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td="bgcolor:#FF0000"]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

1​

[/td][td="bgcolor:#FF0000"]

1​

[/td][td="bgcolor:#FF0000"]

1​

[/td][td="bgcolor:#FF0000"]

1​

[/td][td="bgcolor:#FF0000"]

1​

[/td][td="bgcolor:#FF0000"]

1​

[/td][td]

0​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td="bgcolor:#FF0000"]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td="bgcolor:#FF0000"]

1​

[/td][td="bgcolor:#FF0000"]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[/table]

 

[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?
[TABLE="width: 441"]
<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>[TR]
[TD="width: 58, bgcolor: transparent"] [/TD]
[TD="width: 61, bgcolor: transparent"] [/TD]
[TD="width: 34, bgcolor: transparent"] [/TD]
[TD="width: 15, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"] [/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/1[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/2[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/3[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/4[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/6[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/7[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/8[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/9[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/10[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/11[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/12[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] , align: right"]8/13[/TD]
[/TR]
[TR]
[TD="width: 58, bgcolor: transparent"]EMPLOYE E NUMBER
[/TD]
[TD="bgcolor: transparent"]EMPLOYEE NAME
[/TD]
[TD="bgcolor: transparent"] CLASS
[/TD]
[TD="bgcolor: transparent"] RATE
[/TD]
[TD="width: 69, bgcolor: transparent"] Consecutive Days Worked
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=548235]#548235[/URL] "]In[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA6128[/TD]
[TD="bgcolor: transparent"]ALL, DAVID "RANDY"[/TD]
[TD="bgcolor: transparent"] GF [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA0430[/TD]
[TD="bgcolor: transparent"]ALVARADO, JOSE ANGEL[/TD]
[TD="bgcolor: transparent"] Lead [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA8155[/TD]
[TD="bgcolor: transparent"]ARDOIN, BRANDON[/TD]
[TD="bgcolor: transparent"] Lead [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA0057[/TD]
[TD="bgcolor: transparent"]ARRIAGA MORALES, JUAN[/TD]
[TD="bgcolor: transparent"] Carpenter [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA1964[/TD]
[TD="bgcolor: transparent"]BARULICH, NICHOLAS[/TD]
[TD="bgcolor: transparent"] GF [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA9168[/TD]
[TD="bgcolor: transparent"]BOLDEN, KENNETH[/TD]
[TD="bgcolor: transparent"] Lead [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[chris_sweezy]

Here is a better look at my data... I should have sent better look initially. Might help?
[TABLE="class: cms_table"]
<tbody>[TR]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 61, bgcolor: transparent"][/TD]
[TD="width: 34, bgcolor: transparent"][/TD]
[TD="width: 15, bgcolor: transparent"][/TD]
[TD="width: 69, bgcolor: transparent"][/TD]
[TD="width: 30, align: right"]8/1[/TD]
[TD="width: 30, align: right"]8/2[/TD]
[TD="width: 30, align: right"]8/3[/TD]
[TD="width: 30, align: right"]8/4[/TD]
[TD="width: 30, align: right"]8/6[/TD]
[TD="width: 30, align: right"]8/7[/TD]
[TD="width: 30, align: right"]8/8[/TD]
[TD="width: 30, align: right"]8/9[/TD]
[TD="width: 30, align: right"]8/10[/TD]
[TD="width: 30, align: right"]8/11[/TD]
[TD="width: 30, align: right"]8/12[/TD]
[TD="width: 30, align: right"]8/13[/TD]
[/TR]
[TR]
[TD="width: 58, bgcolor: transparent"]EMPLOYE E NUMBER[/TD]
[TD="bgcolor: transparent"]EMPLOYEE NAME [/TD]
[TD="bgcolor: transparent"] CLASS [/TD]
[TD="bgcolor: transparent"] RATE [/TD]
[TD="width: 69, bgcolor: transparent"] Consecutive Days Worked [/TD]
[TD]In[/TD]
[TD]In[/TD]
[TD]In[/TD]
[TD]In[/TD]
[TD]In[/TD]
[TD]In[/TD]
[TD]In[/TD]
[TD]In[/TD]
[TD]In[/TD]
[TD]In[/TD]
[TD]In[/TD]
[TD]In[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA6128[/TD]
[TD="bgcolor: transparent"]ALL, DAVID "RANDY"[/TD]
[TD="bgcolor: transparent"] GF [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA0430[/TD]
[TD="bgcolor: transparent"]ALVARADO, JOSE ANGEL[/TD]
[TD="bgcolor: transparent"] Lead [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA8155[/TD]
[TD="bgcolor: transparent"]ARDOIN, BRANDON[/TD]
[TD="bgcolor: transparent"] Lead [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA0057[/TD]
[TD="bgcolor: transparent"]ARRIAGA MORALES, JUAN[/TD]
[TD="bgcolor: transparent"] Carpenter [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA1964[/TD]
[TD="bgcolor: transparent"]BARULICH, NICHOLAS[/TD]
[TD="bgcolor: transparent"] GF [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]0[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA9168[/TD]
[TD="bgcolor: transparent"]BOLDEN, KENNETH[/TD]
[TD="bgcolor: transparent"] Lead [/TD]
[TD="bgcolor: transparent"]#[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1[/TD]
[TD="bgcolor: white"]1
[/TD]
[/TR]
</tbody>[/TABLE]

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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][td="bgcolor:#C0C0C0"]

J​

[/td][td="bgcolor:#C0C0C0"]

K​

[/td][td="bgcolor:#C0C0C0"]

L​

[/td][td="bgcolor:#C0C0C0"]

M​

[/td][td="bgcolor:#C0C0C0"]

N​

[/td][td="bgcolor:#C0C0C0"]

O​

[/td][td="bgcolor:#C0C0C0"]

P​

[/td][td="bgcolor:#C0C0C0"]

Q​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

#​

[/td][td="bgcolor:#F3F3F3"]

Name​

[/td][td="bgcolor:#F3F3F3"]

Class​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Cons​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]APA6128[/td][td]Alan[/td][td]GF [/td][td]#[/td][td="bgcolor:#CCFFCC"]

8​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]APA0430[/td][td]Barb[/td][td]Lead [/td][td]#[/td][td="bgcolor:#CCFFCC"]

7​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]APA8155[/td][td]Cain[/td][td]Lead [/td][td]#[/td][td="bgcolor:#CCFFCC"]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]APA0057[/td][td]Dana[/td][td]Carpenter [/td][td]#[/td][td="bgcolor:#CCFFCC"]

12​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]APA1964[/td][td]Eric[/td][td]GF [/td][td]#[/td][td="bgcolor:#CCFFCC"]

8​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]APA9168[/td][td]Fran[/td][td]Lead [/td][td]#[/td][td="bgcolor:#CCFFCC"]

12​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[/table]
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.