I need a formula to calculate delta between 2 dates in an array then add all if in a WW

So I don't even know if this is possible, but I have an array that looks like this:

Machine Repair StartTime EndTime

0000 Scheduled date/time date/time
1111 unscheduled date/time date/time
0000 unscheduled date/time date/time
0000 scheduled date/time date/time


This array has 30+ different machines and 4 repair types. There are also multiple rows for the same machine within the same day and week. So there will be end up being over a thousand rows over time.

What I'm trying to achieve it to calculate the total time a specific machine was down for during a given WorkWeek based on the StartTime and End Time. What gets complicated is some times the Repair duration spans from one week to the next so I need to only count the portion of the repair duration that was in a that specific week.

What gets more complicated is the workweeks are not standard... they run from Sat @19:00 to Sat @19:00.

Is is possible to create a single formula that scans this entire array and adds all of a specific repair type of a certain machine for a given workweek? Or do you have to first calculate the delta into another column then add all of those?

I've exhausted my formula knowledge to get this in one formula with no success and my brain has almost exploded.

Thanks in advance.

Thanks. I uploaded a mock file and explained what I needed in the Reporting tab.

I have figured out a way to do this in a 2-step method but it would be nice to have it in one calculation formula since my 2 step method uses 52 extra columns.