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..

 

[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