IF statement with time over midnight

[clueless]

I am trying to create and IF statement with time. What I have so far is as
follows:
=IF(I2<J2,TRUE,IF(I2="","",FALSE))

I column is beginning time, J column is the finish time.

I am trying to catch time that is entered incorrectly. It works fine until
we have time over midnight. What can I add to the above statement for time
over midnight?

i.e. I J
12:15 12:14 FALSE (which is correct)
23:59 00:02 FALSE (this should be TRUE)
Thanks so much for your help in advance!

 

[Jacob Skaria]

You can only validate that to the maximum allowed difference...The below
formula checks whether the difference between the times is just 1 hour.If so
it will return true. You can change that to suit your requirement..For
example if the maximum allowed difference is 8 hours you can mention the last
part as 8/24 instead of 1/24

=IF(I2<J2,TRUE,IF(I2="","",TODAY()+J2-(TODAY()-1+I2)<1/24))

If this post helps click Yes

 

[clueless]

Thank You! Perfect! Exactly what I was looking for. Thank you so much for
your help!

 

[Ashish Mathur]

Hi,

Try this

=IF(TEXT(I2,"hh:mm AM/PM")<TEXT(J2,"hh:mm AM/PM"),TRUE,IF(I2="","",FALSE))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com