IF statement with time over midnight

C

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!
 
J

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
 
C

clueless

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top