Consecutive days for non Consecutive rows

lynxbci

Board Regular
Joined
Sep 22, 2004
Messages
201
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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??

PersonOff DateBack DateDays OffConsecutive days off
A12/05/202031/05/202020158
B18/05/202019/05/202022
C21/05/202023/05/202033
A01/06/202030/06/202030158
B03/06/202019/06/20201717
A01/07/202031/07/202031158
A01/08/202031/08/202031158
C13/08/202013/08/202011
A01/09/202030/09/202030158
C12/09/202014/09/202033
A01/10/202016/10/202016158
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Puertorekinsam

Active Member
Joined
Oct 8, 2005
Messages
287
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
Joined
Sep 22, 2004
Messages
201
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Thanks, i am away but will test on return. Much obliged
 

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
402
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
Joined
Sep 22, 2004
Messages
201
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,010
Messages
5,545,479
Members
410,686
Latest member
chandraganji
Top