Extract time across columns in blocks of 30 minutes

Hello all,

Hoping you can help me out with the extract of time across columns in 30 minute increments.

So in the example, row 2 starts at 23:38 (column c - arrival). The total time to complete the task is 67 minutes (column D - departed).

Now with the 67 minutes, I would like to spread it out over column P:CN based on the 30 minute increments in falls within.

Again, with row 2, look at cell CI2:CK2. 22 minutes spent in the first 30 minute block, 30 mins for the next block with 15 mins finishing out the entire time.

In row 3, total of 33 mins for the elapsed time. 4 mins in the 1:30 hour with the remaining 29 mins in the 2:00 hour.

The only data that can be messed with is from column M and to the right. Everything from A:L comes from another database system.

Please let me know if I can clear anything else up.

Jeff, I'm confused.

There are duplicate pairs of times ... Q1:R1 and BM1:BN1 just to cite 2 examples. Each row has the same story. This counts the times twice. I feel confident this isn't what you want. For example row 2 is 67 min but the formula in O2 is going to total 134 min.

What am I missing?

Hi Dave and thank you for your time,

You are exactly right, that would double the time and that's not what I want. Now I see I carried the time way too far. I'm trying to cover a 24 hour period in increments of 30 minutes.

The part I thought would give me trouble would be what I job start before midnight and ends the next day. How can I make sure we also account for that scenario.

The 24 hour period should only go to BL, but what about the starting before midnight part? Or maybe BK?

Yes, this looks like it will work perfectly. Thank you so much for your time. I'm going to do some further testing.