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!


--
clueless

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
---------------
Jacob Skaria


"clueless" wrote:

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!


--
clueless

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


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"clueless" wrote:

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!


--
clueless

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

"clueless" wrote in message
...
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!


--
clueless