Hello Community,
I need help as I'm currently struggling to create a formula that calculates working hours (Monday 08:00 to Friday 16:00), weekend hours (Friday 16:00 to Monday 08:00), and then calculating criteria when it's in a control period. If the time frame spans the entire control period that is simple as it's simply subtract the two dates from one another. When the entire window is not in the control period then calculate it how many hours span a working hour and how many span weekend hour. That formula is seen in column D and E above the highlighted portion. My problem is when a time frame starts in the middle of a control period and ends after the control period to then determine how many hours total were in the control period, then whatever hours is remaining split that to calculate the working hours and weekend hours. I have attached a sample spreadsheet of what I'm looking at and ideally it would be great to change the criteria in a separate sheet to call out information.

Thank you for help and guidance.