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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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