Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Now() Function (2000)

    I would like to create a conditional formula in a cell such that if a specific time during a day has not passed, the cell would return one value, and if the time has passed, the cell would return another value. For example, if the current time is not, say, 4:00 PM EDT, the cell would return a "Yes", but if it were past 4:00 PM EDT, the cell would return a "No".

    I have searched for posts under the Now() function, but to no avail.

    Any takers?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Now() Function (2000)

    Try this formula:

    =IF(HOUR(NOW())<=16,"Yes","No")

    The HOUR function returns the hour based on the 24 hour clock, so 16 corresponds to 4 PM.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Now() Function (2000)

    Hans,
    Your solution doesn't appear to differentiate between dates. That is to say, while 4:00 PM has passed for the days October 1-6, it hasn't passed for the 7th-31st. In other words, in my hypothetical, if I had a worksheet of columns A1-AE1 (I believe that's 31) corresponding to the days in October, I would like a formula that would, in this case, return a "No" in cells A1-F1, and a "Yes" in the remainder of the cells. Sorry I wasn't more clear.
    Thanks,
    Jeff

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Now() Function (2000)

    If you enter 01/10/2002 16:00:00 in A1, and =A1+ 1 in B1 and copy that formula to AE1, and then format the range A1:AE1 as "dd" you could use a formula like

    =IF(NOW()<A1,"Yes","No")

    in say B1 and copy it across the BE1.

    See attached

    Andrew C
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Now() Function (2000)

    If you actually want to REPLACE the values in A1-AE1 that have dates with a yes/No you will have to use a macro. I know of no way to change the values of a cell dynamically without a macro.

    Custom format would work IF you had a constant date/fime:
    [<37536.67] "No"; "Yes"
    But I assume you want the date/time to change day to day. Though you could change the custom format with a macro every day and still keep the date values if desired.

    You could calculate a "date" based on the column from a formula to place yes/no in cell:
    =IF(DATE(2002,10,CELL("col",A1))<TODAY()+16/24,"no", "yes")

    If you want Dates in A1-AE1 and values in A2-AE2 to be yes/no enter in A2:
    =IF(A1<=TODAY()+16/24,"No", "Yes")
    and copy it to B2: AE2

    If you want to use conditional formatting to FORMAT the cells in A1-AE1 add the formula to the conditional format:
    =A1<=TODAY()+16/24
    and then format it as desired. In the above, the "no" days will be colored. (This will NOT change the values)

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Now() Function (2000)

    Thanks, Andrew, I just figured it out when I saw your post.

Posting Permissions

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