Results 1 to 6 of 6

Thread: Verifying if current time is within a time range

  1. #1

    Verifying if current time is within a time range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    Try

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

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Try

    =IF(AND(MOD(NOW(),1)>=F2,MOD(NOW(),1)<=G2),TRUE, FALSE)
    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!

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    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.

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    BTW, if you actually want TRUE or FALSE, this is sufficient

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

  6. #6
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •