1. ## 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. ## 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. ## 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. ## 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

5. ## 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. ## 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
•