If I am within the time specified..?
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 |
If I am within the time specified..?
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 |
If I am within the time specified..?
I actually want to check if =now() is in between 2 times..
|
If I am within the time specified..?
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.. |
If I am within the time specified..?
used =IF(B2<A2,OR(A2<NOW(),B2NOW()),AND(A2<NOW(),B2NO W()))
but not getting accurate result |
If I am within the time specified..?
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.. |
If I am within the time specified..?
"... 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 |
If I am within the time specified..?
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.. |
If I am within the time specified..?
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 |
If I am within the time specified..?
Thanks Bernie.. I think its working perfect now.. :)
|
If I am within the time specified..?
=IF(C=2"TRUE","YES",IF(C2="FALSE","NO",""))
I was trying above formula, but not getting result.. Where am I going wrong? |
If I am within the time specified..?
=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? |
All times are GMT +1. The time now is 10:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com