How to identify non-sequential workdays per employee, excluding stat. holidays?

Hi, I hope you can help me with this challenge. I've been trying to find a formula for a couple of days now without success so far.

I'm getting a report from my Time & Attendance system with days taken off as a leave of absence per employee within a period of time. The problem is the report doesn't include weekends or statutory holidays, which makes it hard to identify how many separate leaves of absence an employee took within a period of time.

My report (attached) is giving all days taken off by all employees. The logic I'm trying to use is by identifying the non-sequential days off I can determine how many times an employee took off. The problem is my formula identifies every Friday to Monday as a non-sequential leave, same happens when Mondays or Good Fridays are statutory holidays.

In the file attached I included my formula and the end result I should get. Would really appreciate if you could help fixing my formula.

Thank you!

Hello and welcome to the forum.

Sort by column A (notice that 005871219 in cell A23 is surrounded by 005871219).

Then you can use this formula in D2:

=IFERROR(IF(AND(A1=A2,WORKDAY(C1,1)=C2),"Sequential","Non-Sequential"),"Non-Sequential")

If you want to incorporate holidays, you can put them somewhere else then refer to that range in the WORKDAY function.