Count the Total Minutes per interval based on raw data

Question

Im trying to develop a template that will count the "on queue minutes" of the raw data.

The format would be like this. Ive tried some formula like count if, but it does not count the minutes itself but it count the occurrence per interval.

I need to get the minutes that will fall within the column M.

Let say 00:00-00:30 how many minutes is within the duration that fall within column J and K.

Cell N17 should have 30 because Cell J14:k14 fall within its range

Update,

I manage to include the formula listed Below, but I'm getting an error if in case the the duration is less than 15 minutes, or only 1 minute. Please see screenshot below:

Answer 1

To achieve this I've had to add some helper columns to your data.

N.B. This will not work over multiple days.

First of all I rounded your times to the nearest minute with the following:

=MROUND(J14,1/60/24)

Then I used this formula to 'bucket' your times accordingly into the helper columns for each row

=(IF(AND($M14>=MIN(P$12:P$13),$M14<=MAX(P$12:P$13)),(MAX(P$12:P$13)-$M14),0)+IF(AND($N14>=MIN(P$12:P$13),$N14<=MAX(P$12:P$13)),($N14-MIN(P$12:P$13)),0)+IF(AND($M14<MIN(P$12:P$13),$N14>MAX(P$12:P$13)),(P$13-P$12),0))*1440

Giving me the following results

I then summed each of these columns into my report view and hid all of the helper columns finally giving me:

You can change the intervals to whatever you want. I've done them at half hour intervals.