IF statement with time over midnight

  • Thread starter Thread starter clueless
  • Start date Start date
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!
 
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
 
Back
Top