calculating hours using scheduled workers and time slots.

Hello, I am trying to figure out how to write a formula that will take two time ranges and figure out how many hours was spent based on how many people worked between those two time ranges. for example....

...... [A]......... [B]........ [C].......

1... Joe.........04:00.....12:30
2....mike.....04:00.....10:00
3...Tom......09:00.....17:30

In cell D1 I put 08:00 and E1 is 18:00 as an example. the end result in F1 would be Joe(4.5)+mike(2)+tom(8.5)=15
I think I added that right lol. the deal is that I don't know how to translate this into a formula. column a is name, column b is start and column c is end. it can be 1 row or up to 20 rows with start end data.
anyone know how to translate this into a formula?

Thank you so much. I'm so close to finishing this sheet I'm just dumbfounded on how to do this. I was going to write a formula that puts 1 and 0 for every minute block but with 20 people it could get messy and waste so much space if I ever expand beyond 20 people. my goal is to eventually work up to 670 people but I'll take whatever I can get.

Thanks!

Here you go. I'm unable to post the original sheet because it's on a work computer that does not allow outside drives. I kept it as basic as I can so you can see what i'm trying to achieve.

I put three workers and three time slot examples. End goal would be 670 officers and 20-35 shifts. Don't worry about overlap or counting officers twice. I can change things on my part to make sure that does not happen.

Much appreciated!

v/r
-Richard

The doesn't work because it doesn't take the officer start time into consideration. for example if you change D1 to 02:00 it says officer worked for 10:30 but billed hours was 12:30.

I figured it out.

Please Login or Register  to view this content.

Sorry, posed the formula from typing it on my cellphone as my work would not allow me to post directly to the forum. Anyhow it doesn't matter cause the formula was still wrong.

Here is the proper formula I figured it out.

Please Login or Register  to view this content.