how filter out rows where the end date does not immediately preceed current record

[excelerate2007]

Hello Excel Pros,

I am trying to view all the records of employee absences to determine who has been on a leave for greater then 2 years consecutively. It doesn't count if they were on a leave for 6 months, then came back to work for a day or month, then went back on leave for another period of time. Only employees who have been off for more the 2 years consecutively.

My issue is that the 2 years could include previous leaves/absences, but they must be consecutive. Meaning that if employee 12345 was off from Jan 1, 2021 to today(Mar 9, 2021 or later or undetermined), I would have to check if the previous leave/absence ended on Dec 31, 2020. If the prev absence is not immediately before the start of the current absence, then I would not include it in the calculation to determine if the employee has been off for more than 2 years(730 days).

I have over 3000 rows of data and employees have multiple absences/leaves. Some consecutive and some not. I would need to figure out a way to eliminate the ones that are not consecutive. The important absence period is the current one. Anyone who is currently off, must be checked to see if they had a previous absence immediately before the current absence. If they did, then that absence is included in the calculation of 2 years or 730+ days.

Is there a faster way to do this, with over 3000 rows? Any help is appreciated.

I have pulled the data via SQL and pasted it into Excel.

The attached example, includes five employees with absences. Only the last two employee have multiple absences that should not be included in the 2 year calculation, because the most recent absence does not have an absence immediately before.

Thank you in advance,
beginner excel

[FlameRetired]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[excelerate2007]

Thanks Dave. Much appreciated. Attachment has been attached.

[JeteMc]

This proposal adds two columns F:G or E:F once the current column is is deleted.
Cells D2:D19 are populated using: =C2-B2
Cells F2:F19 are populated using: =IF(A2<>A1,0,B2-C1-1)
Note that column F may be hidden for aesthetic purposes.
Cells G2:G19 displays the consecutive days off and is populated using: =IF(A2=A3,"",IF(F2=0,SUMIFS(D$2:D$19,A$2:A$19,A2,F$2:F$19,0),D2))
Please include your version of Excel in the profile so that we will know what functions are supported. I.E. SUMIFS is not supported by version earlier than Excel 2007.
It would also be good to get a general location as some countries use periods as a decimal separators while others use commas. I.E. US uses period while France uses comma.
Let us know if you have any questions.

[bebo021999]

It reflect exactly what is your expected results:

In E2:

Please Login or Register  to view this content.

But I wonder if your calc of Days# between End & Start date: Start Date - Endate is correct?
For instant: End date (23-Mar) - Start date (23-Mar) = 0
End date (24-Mar) - Start date (23-Mar) = 1

In case 1st one =1 and 2nd one=2

Formula now is shorter:

Please Login or Register  to view this content.