Calculate hours between to times that fall in a shift

[puffin81]

I have a list that contains start and end times, I want to be able to calculate the time proportion that falls in each shift period, ie how each 8 hour shift will be broken up

Eg (using the 06:00 to 14:00 shift for example) times are just examples it would be impossible for them to all be in the same day in reality

A. B. C
Start time. end time hours that fall in 06:00 (to be a formula)
06:00 14:00 8
14:00 15:00 0
06:30 13:30 7
07:00 14:00 7
09:00 10:00 1
22:00 00:00 0
11:00 16:00 5

[scouse13]

Hi bit confused as to what you actually want

based on your example, are you just wanting to show whole hours or minutes as well?

if whole hours then the below should work

Set A and B columns- go to format - Custom, scroll down and click on [h]:mm

Column C -go to format - custom, scroll down and click on [h]

if you want to show minutes then set C the same as A and B

nb the above will not work over 24 hours a different method is needed let me know if this solves what you need

Scouse13

[puffin81]

Sorry for the confusion, I left the sheet I was working on at work and didn’t bring my computer home, then got frustrated.

What I’m looking for is column c to work out the hours between the 2 times, but only if they are within the shift. So in my example I’ve used shift 6 to 14:00. Therefore if I have an activity that starts at 5 and ends at 14:00, column c would calculate 8 hours as the hour between 5 & 6 is in a different 8 hour shift.

I’m looking to round up or down to whole hours to break down the whole 8 shift

[bebo021999]

With start time is in A1, end time is in B1
C1:

Please Login or Register  to view this content.

[puffin81]

That works perfectly for 06:00 to 14:00

However when I try to use the formula for 14:00 to 22:00 it appears not to work, i assumed it would be as simple as changing 6 to 14 and 14 to 12 within the formula

Then my other issue which complicates matters further is 22:00 to 06:00 crosses midnight

[Toonies]

Hi Puffin81

Checkout this post it should help you out

https://www.excelforum.com/excel-for...ml#post5316964