Hi,
I'm trying to use the attached sheet to calculate the time a job takes minus break times.
The problem I am having is when the break crosses midnight.
Any help greatly appreciated.
Taken taken minus breaks over midnight
-
-
Re: Taken taken minus breaks over midnight
I think your formula in I7 is wrong - You're checking the Start time in B7 <= what looks like the end time in I3? Is the same for C7 & I2
Changing that around and another little additon to account for the midnight changeover gets
=IF(AND($B7<=I$2,$C7>=I$3),I3+(I2>I3)-I2,0)
Which seems to give your 1:30 result with 08:00 in I7
-
Re: Taken taken minus breaks over midnight
Hi,
Thanks for having a look.
This does me the correct figure for this job but the problem is it gives me 8hrs over midnight for ALL the jobs now, instead of just the ones that actually go past midnight. -
Re: Taken taken minus breaks over midnight
Sorry - I just concentrated on the one cell - if I get time I'll have a look later.
-
Re: Taken taken minus breaks over midnight
Hi,
The formula in column I needs to have an extra condition to the AND function, to test if the Finish Time is less than the start time, to determine whether or not the shift has spanned midnight.
=IF(AND($B5<=I$2,$C5>=I$3,$C5<$B5),I$3-I$2+1,0)
Hope this helps.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!