Problem resolving a MOD formula

Hi,

I've had a look at your workbook and I don't understand what is happening.

Can you please explain a bit clearer what it is that you are trying to achieve?

You said that E3 (= 20:01) is greater than the booked (A3 = 17:00) and hence L3 should say "N", but E2 (= 19:00) is greater than the booked too (A2 = 14:00), so why shouldn't L2 be "N", and similarly why shouldn't L4 also be "N"???

removing MOD function returns an error. I believe the MOD was used as when times hit 00:00 it through an error and so had to work as 24hr. I may well be wrong as am a novice in formulas and the original MOD formula was supplied by another forum user. It worked fine when there was only Start & Finish A but now we have breaks in loads the second set of timings need to be accounted for which currently they do not.
Just need to know where to place these cells within the current formula.

Originally Posted by ajryan88

Hi,

I've had a look at your workbook and I don't understand what is happening.

Can you please explain a bit clearer what it is that you are trying to achieve?

You said that E3 (= 20:01) is greater than the booked (A3 = 17:00) and hence L3 should say "N", but E2 (= 19:00) is greater than the booked too (A2 = 14:00), so why shouldn't L2 be "N", and similarly why shouldn't L4 also be "N"???

Sorry my oversite. You are correct L2 should also be N (my bad) but L4 should be Y as 18:50 is before 19:00. Formula needs to reflect if load is late compared to booked time

Hi,

Try this formula then:

Formula:

Please Login or Register  to view this content.

Is it that simple? Or does start/finish A need to be considered too?

We are a 24hr operation aswell so when a load is booked for 01:00 and is completed @23:00 (2 hours early) this should reflect as a Y (on time). I think this is why a MOD formula was used initially

Originally Posted by ajryan88

Hi,

Try this formula then:

Formula:

Please Login or Register  to view this content.

Is it that simple? Or does start/finish A need to be considered too?

As mentioned would this still read true for the cross midnight loads

Hi,

Have you considered implementing dates and times instead of just times??

My formula above will work across midnight loads IF dates are entered as well. In fact, it will make your life a lot easier to insert the dates as well, because without the dates the formula that would be required would have to take care of a lot of conditions that could easily be avoided if a date was present.

Hope this helps

NO sorry a date cannot be incorperated as the shift works 18:00 to 06:00 and will liast on sheets as only 1 date.

Originally Posted by Norie

Did you try just removing MOD?

=IF(C2-B2>A2-B2,"N","Y")

Yes but this will not work wth cross midnight loads. As an 01:00 load completed @23:00 will show as N (late) where in fact it was 2 hours early. Unless im missing something ???

Hi,

Try this then:

Formula:

Please Login or Register  to view this content.

Hope this works

By changing the order of calculation will not work on all times.
Your formula works for same date only but we cross midnight, and the booked times wont change. But they may be different booked times on different days which is why i need 1 formula to work across the midnight area.
at present your formula works with the example I gave, but if u changed Cell A4 to 01:00 and completed time to 23:00 it returns as a N (late)

Originally Posted by ajryan88

Hi,

Try this then:

Formula:

Please Login or Register  to view this content.

Hope this works

That looks like the one that works. will trial it and give feed back. thanx guys

No problems!

If this solves your issue, please be sure to let the forum know by marking this thread as solved, and please click on the * next to any post(s) that were helpful to you.

Thanks

Ive crossed another dilemma with this formula.

If the load is completed in the first section (cells B2 - C2) then the E cell remains blank and the formula doesnt pick up the finish time. Any suggestions?

Try this slightly modified formula then:

Formula:

Please Login or Register  to view this content.

That seems to be the result i was looking for. Thanx aj