Taken taken minus breaks over midnight

  • 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.

    Files

    • Time Taken.xlsx

      (10.36 kB, downloaded 95 times, last: )
  • 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


    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.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]