Verifying if current time is within a time range

Hi all. I need a formula that looks at the current time and can determine whether that time is between two other times. It needs to be dynamic. What I did was create a Start Time (col F) and a Grace Period (col G) which varies by line and always has a 15 min. spread between the two. In col K, I entered =Now() to get the current time (understanding that this needs to be recalced). I then created a col H current Schedule to act as a flag of either True or False if the current time falls (or doesn't fall) between the Start Time and Grace Period. However it only returns False. Here is the formula I am using: =IF(AND($K$2>=F2,$K$2<=G2),TRUE, FALSE) I think the problem is when the formula is evaluating $K$2 that even though the actual time is displayed it is interpreting the contents of the cell as =Now() and that doesn't compute against the time range. I think this because if I key in an actual time in K2 the formula works fine.

So that was a long winded way of saying is there a way to insert in a formula a reference to the actual result another formula returns rather than the actual formula itself?

Thanks in advance!

Lou

Try

=IF(AND(MOD(NOW(),1)>=F2,MOD(NOW(),1)<=G2),TRUE, FALSE)

Bob Phillips said:

Try

=IF(AND(MOD(NOW(),1)>=F2,MOD(NOW(),1)<=G2),TRUE, FALSE)

Click to expand...

Hi Bob,

Thanks, that appears to have worked. I'll know for sure after some more testing but so far so good. And it eliminated the need for that separate cell with the =Now() formula which would have been my next question about combining terms.

Can you explain why the MOD function worked? I read the use of this and I'm not seeing the correlation.

Thanks again!

The NOW() function returns a time and a date, whereas you are comparing just against a time, so you are asking for example if .5 (12:00PM) <= 42042.7723137731 (18:37 today), it just never can be because of the day number. By taking MOD(NOW(),1), you are stripping the date off and just leaving the current time.

BTW, if you actually want TRUE or FALSE, this is sufficient

=AND(MOD(NOW(),1)>=F2,MOD(NOW(),1)<=G2)

Ha! Now I got it. Isn't wasn't the formula that was causing the problem for me so much as what the formula truly returns (date and time) which I had forgotten since I masked it with just a time format.

Thanks for the solution, the explanation and the shortened version!