Consecutive days for non Consecutive rows

[lynxbci]

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

 

[Puertorekinsam]

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.

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]

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

 

[Dossfm0q]

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
	A	B	C	D	E
1	Person	Off Date	Back Date	Days Off	Consecutive days off
2	A	12/05/2020	31/05/2020	20	158
3	B	18/05/2020	19/05/2020	2	2
4	C	21/05/2020	23/05/2020	3	3
5	A	01/06/2020	30/06/2020	30	158
6	B	03/06/2020	19/06/2020	17	17
7	A	01/07/2020	31/07/2020	31	158
8	A	01/08/2020	31/08/2020	31	158
9	C	13/08/2020	13/08/2020	1	1
10	A	01/09/2020	30/09/2020	30	158
11	C	12/09/2020	14/09/2020	3	3
12	A	01/10/2020	16/10/2020	16	16
Sheet2

Cell Formulas
Range	Formula
D2:D12	D2	=C2-B2+1
E2:E12	E2	=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
	A	B	C	D	E
1	Person	Off Date	Back Date	Days Off	Consecutive days off
2	A	12/05/2020	31/05/2020	20	156
3	B	18/05/2020	19/05/2020	2	2
4	C	21/05/2020	23/05/2020	3	3
5	A	01/06/2020	30/06/2020	30	156
6	B	03/06/2020	19/06/2020	17	17
7	A	01/07/2020	31/07/2020	31	31
8	A	03/08/2020	31/08/2020	29	156
9	C	13/08/2020	13/08/2020	1	1
10	A	01/09/2020	30/09/2020	30	156
11	C	12/09/2020	14/09/2020	3	3
12	A	01/10/2020	16/10/2020	16	16
Sheet2

Cell Formulas
Range	Formula
D2:D12	D2	=C2-B2+1
E2:E12	E2	=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]

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
	A	B	C	D	E
1	Person	Off Date	Back Date	Days Off	Consecutive days off
2	A	12/05/2020	31/05/2020	20	158
3	B	18/05/2020	19/05/2020	2	2
4	C	21/05/2020	23/05/2020	3	3
5	A	01/06/2020	30/06/2020	30	158
6	B	03/06/2020	19/06/2020	17	17
7	A	01/07/2020	31/07/2020	31	158
8	A	01/08/2020	31/08/2020	31	158
9	C	13/08/2020	13/08/2020	1	1
10	A	01/09/2020	30/09/2020	30	158
11	C	12/09/2020	14/09/2020	3	3
12	A	01/10/2020	16/10/2020	16	16
Sheet2

Cell Formulas
Range	Formula
D2:D12	D2	=C2-B2+1
E2:E12	E2	=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
	A	B	C	D	E
1	Person	Off Date	Back Date	Days Off	Consecutive days off
2	A	12/05/2020	31/05/2020	20	156
3	B	18/05/2020	19/05/2020	2	2
4	C	21/05/2020	23/05/2020	3	3
5	A	01/06/2020	30/06/2020	30	156
6	B	03/06/2020	19/06/2020	17	17
7	A	01/07/2020	31/07/2020	31	31
8	A	03/08/2020	31/08/2020	29	156
9	C	13/08/2020	13/08/2020	1	1
10	A	01/09/2020	30/09/2020	30	156
11	C	12/09/2020	14/09/2020	3	3
12	A	01/10/2020	16/10/2020	16	16
Sheet2

Cell Formulas
Range	Formula
D2:D12	D2	=C2-B2+1
E2:E12	E2	=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)