IF Function - Greater Than, Less Than with Times

[MiglA06]

I need help figuring out why the below formula is not returning the right value. I'm using the formula to determine if trailers are being loaded on time. We use military time for our times and the formula works fine until you reach 00:00. After that everything shows up as late or on time depending on the time. The cells are formatted for time but I still get the same results. In the example you can see that the LRT is 15:30 and Ready Time was 15:00 so trailer was ON TIME. LRT on next one was also 15:30 and Ready Time was 02:36(am) so trailer should've been late but i get on Time. The same for the other two. The last two should both say On Time because they were both done before 00:00 but the 17:30 says late. I've looked but can't seem to find a solution. Any help will be greatly appreciated.


N3 P3 R3
LRT Ready Time Trailer Status

15:30 15:00 ON TIME
15:30 02:36 ON TIME

00:00 17:30 LATE
00:00 23:15 ON TIME

Formula on R3
=IF(P3<=N3,"ON TIME",IF(P3>N3,"LATE"))

[FDibbins]

Hi, welcome to the forum

LRT on next one was also 15:30 and Ready Time was 02:36(am) so trailer should've been late but i get on Time.

N3=15:30
P3=02:36
=IF(P3<=N3,"ON TIME"
P3 is indeed < N3, so the answer should be On Time, the same as for P2=15:00

Seeing as you only really have 2 tests, the 2nd IF is redundant
=IF(P3<=N3,"ON TIME","LATE")

[MiglA06]

Hi FDibbins,

Thanks for the welcome and reply. I've been looking at it and I don't think there is a way to solve my problem. You are right the formula is working because technically 2am is earlier that 15:30 therefore it's on time. The problem is that it's 2am the next day. The 15:30 is the day prior so the 2am ready time is Late. I've been searching but I don't thinks there's a way around that.

[bebo021999]

If 2:36 is next day, you should store it as 26:36 ( and format as h:mm to display 2:36 if needed)
Similar to 0:00 is 24:00
then use your formula.
Another way, if hour of next day never exceed, i.e 6:00 AM of next day, use this:
N3 should be: = N3+IF(N3<=6/24,1,0)
P3 should be: =P3+IF(P3<6/24,1,0)
Then use:

=IF(N3+IF(N3<=6/24,1,0)-(P3+IF(P3<6/24,1,0))>0,"ON TIME","LATE")

[MiglA06]

Thanks bebo021999. That formula worked perfectly. Thanks for your help.

[MiglA06]

I ran into a problem. The formula works perfect but I just realized that sometimes P3 will stay blank and N3 will not and I need for those to say PENDING. Can I add an additional IF Function to the original to accomplish this?

=IF(N3+IF(N3<=7/24,1,0)-(P3+IF(P3<7/24,1,0))>0,"ON TIME","LATE")


P3.png

[AliGW]

Try this:

=IF(AND(P3="",N3<>""),"PENDING",IF(N3+IF(N3<=7/24,1,0)-(P3+IF(P3<7/24,1,0))>0,"ON TIME","LATE"))

[MiglA06]

Thank you so much AliGW . That worked great.

[AliGW]

No problem.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.