Calculating hours in periods

[XCESIV]

I have a simple query i am sure of it

I have 2 Columns starting at C3 and E3.

In C3 is the start time, and E3 the finish time, and this goes down the page for each team member

For each team member i need to calculate how many hours they have been rostered in certain periods of a day.

1) Midnight till 7am (Answer in Column J:J)
2) 7am Till 7pm (Answer in Column K:K)
3) 7pm till Midnight (Answer in Column L:L)

So if a Shift listed in C3 has a start time of 7am till 5pm, thats 10 hours in the 7am Till 7pm Category.

If a Shift is 5pm till Midnight, that gets split.
2x Hrs in the 7am Till 7pm and 5x Hrs in 7pm till Midnight

Can someone help me with this please

thank you

Warren

[shg]

Post a workbook.

[DonkeyOte]

Warren, not perfect by any means but does this help ?

I have no doubt shg and co can streamline but this may get you going nonetheless.