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

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

=IF(IB2<A2,OR(A2<C2,B2C2),AND(A2<C2,B2C2))

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

=IF(IB2<A2,OR(A2<C$2,B2C$2),AND(A2<C$2,B2C$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" wrote in message
...
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

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

Then simply replace all reference to C2 with NOW()

HTH,
Bernie
MS Excel MVP

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

used =IF(B2<A2,OR(A2<NOW(),B2NOW()),AND(A2<NOW(),B2NO W()))

but not getting accurate result

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,B2D2),AND(A2<D2,B2D2))

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

"... 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(),B2NOW()) want to be AND(B2<NOW(),A2NOW()) ?

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

Kashyap wrote:
used =IF(B2<A2,OR(A2<NOW(),B2NOW()),AND(A2<NOW(),B2NO W()))

but not getting accurate result

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.?
--
David Biddulph

Kashyap wrote:
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,B2D2),AND(A2<D2,B2D2))

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

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" wrote in message
...
used =IF(B2<A2,OR(A2<NOW(),B2NOW()),AND(A2<NOW(),B2NO W()))

but not getting accurate result

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

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

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

=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


"Kashyap" wrote in message
...
=IF(C=2"TRUE","YES",IF(C2="FALSE","NO",""))

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