Working out times over midnight with a twist

We have A1 (Requested time) B1 (Allocated) time C1 (Started time) D1 (Completed time)

I am looking to report on two things (time taken to allocate a job, relative to the requested time) & Time taken from start to complete of the job.

I am using =MOD(B1-A3,1) so that any jobs requested/started before midnight and allocated/completed after midnight still give a value.

However we sometimes allocate a job before the requested time (usually just a few minutes).

Is there any way i can work my formula to provide a zero value if the requested time after the allocated time?

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Hi there, try this:

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

You're welcome... from windy Belfast!!

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)

Don't rows 2 & 3 cover your scenario? that said, your formula is 50% quicker than mine...

Hello Glenn,

No, there are no examples in the data of that scenario.

Custhasno wanted a formula that will return zero in a scenario like row 19 where request time/date is 6 minutes after Allocated time/date, so if that 6 minutes occurred across midnight (which I assume may be possible) then shouldn't the answer still be zero, e.g. if the request time/date in row 55 is 10 minutes later, i.e. 22/10/2017 at 00:05, but Allocated time remains at 21/10/2017 at 23:59?

That's another instance of the request time being after the allocated time, but your formula gives 23:54

Sigh!! As usual you're right.... Obviously we don't know what the background is, in full, but there are indeed many instances where the allocated D/T is < the requested D/T.

Exit stage left, in pursuit of a beer - as Shakespeare intended to write....