Can I use the NOW() function in a formula?

  • Thread starter Thread starter Smith512
  • Start date Start date
S

Smith512

I have a cell B1 that has NOW() time and another cell B2 that has NOW() date.
I am trying to have data from another cell F1 brought in to the destination
cell B3,
when a given date and time occur.
=(IF(AND(B1="23:00:00",B2="12/03/2009"),F1," ")
This formulas does not work.
The Clock is continuously active / always changing.
Any suggestions would be appreciated. Thanks.
 
The NOW() function returns both a date and a time. Even if you format the
cell to display only date/time, all the data is still retained. NOte that if
you did want just the date, you could use the TODAY() function. Your formula
literally corrected would be:


=IF(NOW()=TIMEVALUE("23:00:00")+DATEVALUE("12/03/2009"),F1," ")

The problem is, this criteria would only be met for a few nanoseconds!
Keeping in mind that time is stored as a decimal portion of a number, perhaps
what you really want is
=IF(AND(TODAY()=DATEVALUE("12/03/2009"),MOD(NOW(),1)>TIMEVALUE("23:00:00")),F1," ")

Note that if you change the date and time checks to cell references, it
would make this formula slightly shorter.
 
At 3:33 PM today I entered this into a cell
=IF(TIME(HOUR(NOW()),MINUTE(NOW()),0)>=TIME(15,37,0),"X","not yet")
It displayed NOT YET
I pressed F9 every 10 seconds to recalculate the worksheet
When the time on taskbar showed 3:37 PM the cell displayed X

For the Date I would use DATE(2009,12,13) rather than DATEVALUE to be
date-format independent

=IF(AND(TODAY()=DATE(2009,12,13),TIME(HOUR(NOW()),MINUTE(NOW()),0)>=TIME(15,37,0),F1,"
")

best wishes
 
Back
Top