Counting consecutive days based on current date...

chris_sweezy

New Member
Joined
Aug 25, 2018
Messages
14
I'm looking for formula in "Consecutive Days Worked" column that will count consecutive days worked(1=worked 0=not worked). Challenge is employee cant work more than 13 consecutive days, so count needs to start over when it hits a 0, and be tied to current date.
Thank you for your time.

[TABLE="width: 676"]
<colgroup><col width="70" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2784;"> <col width="163" style="width: 122pt; mso-width-source: userset; mso-width-alt: 6528;"> <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="7"> <col width="26" style="width: 20pt; mso-width-source: userset; mso-width-alt: 1056;" span="13"> <tbody>[TR]
[TD="width: 70, bgcolor: transparent"]EMPLOYEE NUMBER
[/TD]
[TD="width: 163, bgcolor: transparent"]EMPLOYEE NAME[/TD]
[TD="width: 34, bgcolor: transparent"]CLASS[/TD]
[TD="width: 15, bgcolor: transparent"] [/TD]
[TD="width: 69, bgcolor: transparent"]Consecutive Days Worked[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: right"]8/19[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: right"]8/20[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: right"]8/21[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: right"]8/22[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: right"]8/23[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: right"]8/24[/TD]
[TD="width: 30, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: right"]8/25[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]8/26[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]8/27[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]8/28[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]8/29[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]8/30[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]8/31[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]9/1[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]9/2[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]9/3[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]9/4[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]9/5[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]9/6[/TD]
[TD="width: 26, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] "]9/7[/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: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/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: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/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: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/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: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/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: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/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: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA8349[/TD]
[TD="bgcolor: transparent"]BROWN, JASON[/TD]
[TD="bgcolor: transparent"]Foreman[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]APA3413[/TD]
[TD="bgcolor: transparent"]BROWN, ROBERT[/TD]
[TD="bgcolor: transparent"]Lead[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Let's try this. It calculates the Maximum Consecutive Days worked in each record. This is a common formula to find streaks with conditions.


Book1
ABCDEFGHIJKLMNOPQRSTU
1EMPLOYEE NUMBEREMPLOYEE NAMECLASSMax Consecutive Days Worked19-Aug20-Aug21-Aug22-Aug23-Aug24-Aug25-Aug26-Aug27-Aug28-Aug29-Aug30-Aug31-Aug01-Sep02-Sep03-Sep04-Sep
2APA6128ALL, DAVID "RANDY"GF1211111111111100000
3APA0430ALVARADO, JOSE ANGELLead301110111011100000
4APA8155ARDOIN, BRANDONLead000000000000000000
5APA0057ARRIAGA MORALES, JUANCarpenter711111110111100000
6APA1964BARULICH, NICHOLASGF701111111011100000
7APA9168BOLDEN, KENNETHLead801101111111100000
8APA8349BROWN, JASONForeman901011111111100000
9APA3413BROWN, ROBERTLead1101111111111100000
Sheet30
Cell Formulas
RangeFormula
D2{=MAX((FREQUENCY(IF(E2:U2=1,COLUMN(E2:U2)),IF(E2:U2<>1,COLUMN(E2:U2)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Let's try this. It calculates the Maximum Consecutive Days worked in each record. This is a common formula to find streaks with conditions.

ABCDEFGHIJKLMNOPQRSTU
APA6128ALL, DAVID "RANDY"GF
APA0430ALVARADO, JOSE ANGELLead
APA8155ARDOIN, BRANDONLead
APA0057ARRIAGA MORALES, JUANCarpenter
APA1964BARULICH, NICHOLASGF
APA9168BOLDEN, KENNETHLead
APA8349BROWN, JASONForeman
APA3413BROWN, ROBERTLead

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]EMPLOYEE NUMBER[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]EMPLOYEE NAME[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]CLASS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Max Consecutive Days Worked[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]19-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]20-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]21-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]22-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]23-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]24-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]25-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]26-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]27-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]28-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]29-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]30-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]31-Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]01-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]02-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]03-Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]04-Sep[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet30

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=MAX((FREQUENCY(IF(E2:U2=1,COLUMN(E2:U2)),IF(E2:U2<>1,COLUMN(E2:U2)))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Yes sir... it works.. Thank you. Could the formula be modified to count the consecutive days exactly as above, but based on current date? If so, the cell range would not have to be modified, as the calculation would change when date changes. I apologize if not explaining well.... I do appreciate your help and patience. Thanks again
 
Upvote 0
What exactly do you mean by "based on current date"? Using our current dataset, indicate the expected outcome for some of the records.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top