If I am within the time specified..?

[Kashyap]

Hi, I need to find out if I am within the time specified..

ColA has start time and ColB has end time..

Col A Col B
17:30 04:30
18:00 05:00
16:30 03:30
11:30 22:30

 

[Bernie Deitrick]

With the time of interest in column C for each row:

=IF(IB2<A2,OR(A2<C2,B2>C2),AND(A2<C2,B2>C2))

or with the time of interest in C2 for All rows:

=IF(IB2<A2,OR(A2<C$2,B2>C$2),AND(A2<C$2,B2>C$2))

And if, by within, you mean also that your time could equal any of them,
then change all

to >=, and all < to <=

Then copy the formula down to match your time limits.

HTH,
Bernie
MS Excel MVP

 

[Kashyap]

I actually want to check if =now() is in between 2 times..

 

[Bernie Deitrick]

Then simply replace all reference to C2 with NOW()

HTH,
Bernie
MS Excel MVP

 

[Kashyap]

used =IF(B2<A2,OR(A2<NOW(),B2>NOW()),AND(A2<NOW(),B2>NOW()))

but not getting accurate result

 

[Kashyap]

17:30 3:30 TRUE 4:46
18:00 5:00 TRUE 4:46
16:30 3:30 TRUE 4:46
11:30 22:30 FALSE 4:46

=IF(B2<A2,OR(A2<D2,B2>D2),AND(A2<D2,B2>D2))

in the above case, only 2nd should be true..

 

[David Biddulph]

"... not getting accurate result ..." is not a very specific description of
your problem.
What were the values of A2, B2, and NOW() at the time of calculation, what
result did you get, and what result did you expect?
Why are you using OR for one case and AND for the other?
If you are trying to test for NOW() being between A2 and B2, doesn't your
OR(A2<NOW(),B2>NOW()) want to be AND(B2<NOW(),A2>NOW()) ?

Isn't it easier to use =NOW()=MEDIAN(A2,NOW(),B2) ?
But perhaps you want somewhat different results for some of the marginal
cases, where two or more of the 3 values are equal ?

 

[David Biddulph]

In that case, only the 2nd *is* true, so your formula seems to be doing the
job (but doesn't give the values you show in your 3rd column).
If you are having problems, check that your times are all true Excel times
and not text, and that they don't also include date data (because your
formula assumes that they don't). Format the times temporarily as General
or Number and check whether any are greater than 1, as 12:00 is 0.5 of a day
and 23:59 is nearly 1.

The situation is different from what I had guessed from your earlier
incomplete description to which I tried to reply. I assume now that A is
your start time and B your finish time, and that these might span midnight.?

 

[Bernie Deitrick]

Sorry, I never use volatile time functions, so I forgot that NOW includes
the date:

=IF(B2<A2,OR(A2<(NOW()-TODAY()),B2>(NOW()-TODAY())),AND(A2<(NOW()-TODAY()),B2>(NOW()-TODAY())))

or, instead of
NOW()-TODAY()
use
MOD(NOW(),1)
or
NOW()-INT(NOW())

Bernie

 

[Kashyap]

Thanks Bernie.. I think its working perfect now..

 

[Kashyap]

=IF(C=2"TRUE","YES",IF(C2="FALSE","NO",""))

I was trying above formula, but not getting result.. Where am I going wrong?

 

[Bernie Deitrick]

=IF(C=2"TRUE","YES",IF(C2="FALSE","NO",""))

maybe?

=IF(C2,"YES","NO")

To catch C2 being empty:
=IF(C2="","",IF(C2,"YES","NO"))

To catch C2 being empty, or filled with something not a boolean TRUE/FALSE
=IF(C2="","",IF(ISERROR(IF(C2,,)),"Error", IF(C2,"YES","NO")))

HTH,
Bernie
MS Excel MVP