# Consecutive days for non Consecutive rows

#### lynxbci

##### Board Regular
Hi,

Consider this table of employees off sick. Person A has had several periods of time off, and they happen to be all consecutive, totalling 158 days.
I am struggling for a formula/Method to return the number of consecutive days Person A has had off??

 Person Off Date Back Date Days Off Consecutive days off A 12/05/2020 31/05/2020 20 158 B 18/05/2020 19/05/2020 2 2 C 21/05/2020 23/05/2020 3 3 A 01/06/2020 30/06/2020 30 158 B 03/06/2020 19/06/2020 17 17 A 01/07/2020 31/07/2020 31 158 A 01/08/2020 31/08/2020 31 158 C 13/08/2020 13/08/2020 1 1 A 01/09/2020 30/09/2020 30 158 C 12/09/2020 14/09/2020 3 3 A 01/10/2020 16/10/2020 16 158

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Puertorekinsam

##### Active Member
Here is some code that will do what you are looking for. This makes an assumption that the new records are always in chronological order, that there is never any overlap on BackDate and Days off, and that the "Days off column" is pre-populated.

Code:
``````Sub DaysInARow()
Dim Person As Integer
Dim PersonCount As Integer
Dim MaxBackDate As Date
Dim ConsDays As Integer
Dim Person2 As Integer
Person = 2

Columns(5).Clear
Cells(1, 5) = "Consecutive days off"

Do Until Cells(Person, 1) = ""
ReDim PersonArray(0)
PersonCount = 0
PersonArray(0) = Person
MaxBackDate = Cells(Person, 3)
ConsDays = Cells(Person, 4)
EndPerson2 = True
Person2 = Person + 1
Do Until Cells(Person2, 1) = "" Or EndPerson2 = False Or Cells(Person, 5) <> ""
If Cells(Person, 1) = Cells(Person2, 1) Then
'check for the same person
If MaxBackDate + 1 = Cells(Person2, 2) Then
'CHECK FOR CONSECITIVE DAYS
MaxBackDate = Cells(Person2, 3)
ConsDays = ConsDays + Cells(Person2, 4)
ReDim Preserve PersonArray(UBound(PersonArray) + 1)
PersonArray(UBound(PersonArray)) = Person2
Else
EndPerson2 = False
End If
End If

Person2 = Person2 + 1
Loop

Dim i As Integer
If Cells(Person, 5) = "" Then
For i = 0 To UBound(PersonArray)
Cells(PersonArray(i), 5) = ConsDays
Next
End If
Person = Person + 1
Loop

End Sub``````

#### lynxbci

##### Board Regular
Thanks, i am away but will test on return. Much obliged

#### Dossfm0q

##### Active Member
Greetings
On this topic, I wrote down this somewhat correct formula, but it does not fulfill the conditions of the Consecutive if one of the elements of the Consecutive changes
Please rewrite it better

Thanks

Book1
ABCDE
1PersonOff DateBack DateDays OffConsecutive days off
2A12/05/202031/05/202020158
3B18/05/202019/05/202022
4C21/05/202023/05/202033
5A01/06/202030/06/202030158
6B03/06/202019/06/20201717
7A01/07/202031/07/202031158
8A01/08/202031/08/202031158
9C13/08/202013/08/202011
10A01/09/202030/09/202030158
11C12/09/202014/09/202033
12A01/10/202016/10/20201616
Sheet2
Cell Formulas
RangeFormula
D2:D12D2=C2-B2+1
E2:E12E2=IF(INDEX(\$B\$2:\$B\$12,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$12)-ROW(\$A\$2)+1)/(\$A\$2:\$A\$12=A2),IF(COUNTIF(\$A\$2:A2,A2)<COUNTIF(\$A\$2:\$A\$12,A2),COUNTIF(\$A\$2:A2,A2)+1,COUNTIF(\$A\$2:A2,A2))))-C2=1,SUMPRODUCT((\$A\$2:\$A\$12=A2)*\$D\$2:\$D\$12),C2-B2+1)

Like Below

Book1
ABCDE
1PersonOff DateBack DateDays OffConsecutive days off
2A12/05/202031/05/202020156
3B18/05/202019/05/202022
4C21/05/202023/05/202033
5A01/06/202030/06/202030156
6B03/06/202019/06/20201717
7A01/07/202031/07/20203131
8A03/08/202031/08/202029156
9C13/08/202013/08/202011
10A01/09/202030/09/202030156
11C12/09/202014/09/202033
12A01/10/202016/10/20201616
Sheet2
Cell Formulas
RangeFormula
D2:D12D2=C2-B2+1
E2:E12E2=IF(INDEX(\$B\$2:\$B\$12,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$12)-ROW(\$A\$2)+1)/(\$A\$2:\$A\$12=A2),IF(COUNTIF(\$A\$2:A2,A2)<COUNTIF(\$A\$2:\$A\$12,A2),COUNTIF(\$A\$2:A2,A2)+1,COUNTIF(\$A\$2:A2,A2))))-C2=1,SUMPRODUCT((\$A\$2:\$A\$12=A2)*\$D\$2:\$D\$12),C2-B2+1)

#### lynxbci

##### Board Regular
Hi, I like the solution, however, the last line (row 12) is also consecutive for person A, so would need to be 156 days too, and as that's the latest absence its the most important one to be right.

Kev

Greetings
On this topic, I wrote down this somewhat correct formula, but it does not fulfill the conditions of the Consecutive if one of the elements of the Consecutive changes
Please rewrite it better

Thanks

Book1
ABCDE
1PersonOff DateBack DateDays OffConsecutive days off
2A12/05/202031/05/202020158
3B18/05/202019/05/202022
4C21/05/202023/05/202033
5A01/06/202030/06/202030158
6B03/06/202019/06/20201717
7A01/07/202031/07/202031158
8A01/08/202031/08/202031158
9C13/08/202013/08/202011
10A01/09/202030/09/202030158
11C12/09/202014/09/202033
12A01/10/202016/10/20201616
Sheet2
Cell Formulas
RangeFormula
D2:D12D2=C2-B2+1
E2:E12E2=IF(INDEX(\$B\$2:\$B\$12,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$12)-ROW(\$A\$2)+1)/(\$A\$2:\$A\$12=A2),IF(COUNTIF(\$A\$2:A2,A2)<COUNTIF(\$A\$2:\$A\$12,A2),COUNTIF(\$A\$2:A2,A2)+1,COUNTIF(\$A\$2:A2,A2))))-C2=1,SUMPRODUCT((\$A\$2:\$A\$12=A2)*\$D\$2:\$D\$12),C2-B2+1)

Like Below

Book1
ABCDE
1PersonOff DateBack DateDays OffConsecutive days off
2A12/05/202031/05/202020156
3B18/05/202019/05/202022
4C21/05/202023/05/202033
5A01/06/202030/06/202030156
6B03/06/202019/06/20201717
7A01/07/202031/07/20203131
8A03/08/202031/08/202029156
9C13/08/202013/08/202011
10A01/09/202030/09/202030156
11C12/09/202014/09/202033
12A01/10/202016/10/20201616
Sheet2
Cell Formulas
RangeFormula
D2:D12D2=C2-B2+1
E2:E12E2=IF(INDEX(\$B\$2:\$B\$12,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$12)-ROW(\$A\$2)+1)/(\$A\$2:\$A\$12=A2),IF(COUNTIF(\$A\$2:A2,A2)<COUNTIF(\$A\$2:\$A\$12,A2),COUNTIF(\$A\$2:A2,A2)+1,COUNTIF(\$A\$2:A2,A2))))-C2=1,SUMPRODUCT((\$A\$2:\$A\$12=A2)*\$D\$2:\$D\$12),C2-B2+1)

Replies
7
Views
59
Replies
10
Views
86
Replies
3
Views
67
Replies
1
Views
32
Replies
1
Views
44