Tracking Holiday Across Multiple Weeks

[hairdyeginger]

Hi all,

I'm trying to produce a report which tracks the holidays of individuals per week and flagging if they have more than 3 days holiday booked in the week. i.e. if they have 15/05, 16/05, 17/05 all booked off it will flag that they have 3 days booked off in W/C 15/05.

The data points I have for this are the inividual's details, the start date of the holiday, the end date and the duration in days.

The issue I'm running into is how to flag an individual who has more than 3 days holiday in multiple weeks - i.e. they started their 10 day holiday on 15/05 so they have the 7 days in W/C 15/05 and 3 in W/C 22/05.

I'm currently using a pivot table to group the dates in weeks based off of holiday start dates as I believe this is easiest for the target audience who this report will be going to - I'm sure I'm missing something super obvious or going about this in the wrong way - all help would be gladly received.

Thanks!

 

[hairdyeginger]

An example of what I mean, here employee 53748 has 11 days holiday starting between 22/05/2022 - 28/05/2022 - and this has marked the more than 3 days in that period but hasn't marked the 3 days holiday which will be between 29/05/22 - 04/06/22.