Consecutive Days Calculation

Hi Team, I'm looking to perform a consecutive leave calculation of the employee. The Emp's who have 3 or more consecutive sick leaves, those emp's start and End date of the leaves to be displayed. In this scenario, their might be public holiday's in between. The start and End date of the leaves should not begin or end with the public holidays.
Attaching the input file , holiday file and Expected output file for your reference. Please suggest a solution.

Dummy holiday.xlsx

Expected output_ch.xlsx

Input file_ch.xlsx

Date Time

Accepted answers

Qiu

@Padashwi
Let me give a try also.

I used the Multi-row formula tool to segregate the "Groups" for the same person, and the date is consecutive.
Then we can sample the first and last date for each group and check if it is public holiday.
Join back the original data stream to rule out the public holidays as Left joint.