Working out times over midnight with a twist

Hi there, try this:

=IF(AND(A2=C2,D2<B2),0,MOD(D2-B2,1))

Originally Posted by Glenn Kennedy

=IF(AND(A2=C2,D2<B2),0,MOD(D2-B2,1))

Surely this won't work as required if allocated time is just before midnight one day and request time just after midnight the next?

As you have the dates as well as the times you don't really need to use MOD, the time difference is just the allocated time/date minus the requested time/date, so this formula will give you that with MAX used to ensure you get zero when the allocated time/date is before the requested time/date

=MAX(0,D2+C2-B2-A2)