Now Formula

[Hamed parhizkar]

When I put in =NOW() it outputs the date and time; however, the time does not
stay current. Meaning if I do the formula at 10:30, it shows the date then
10:30, but then at 10:31 it stil shows 10:30.... how do you make the time
continuous?

Thanks

 

[Jim Thomlinson]

Functions like now are referred to as volatile. That means that they are
recalculated each time XL performs a calculation. If XL does not perform a
calculation then the value is not updated. There is no easy way to force XL
to recalc on a regularily scheduled basis, so there is no easy way to make
the now formula act like a clock.

 

[egun]

The easiest way to get Now() to update is to recalculate the worksheet (F9).
You will then see the time change.

If you want it to update itself dynamically, you have to have something
running in the background that periodically checks the time, and if the
minute changes (or whatever you want), then that cell's value is updated or
recalculated. I'm not aware of ways to have background processes running
inside (or outisde) Excel. Maybe someone else can help with that.

Eric

 

[Mike H]

Hi,

For a non volatile date
Ctrl+;
Non volatile time
Ctrl+Shift+;

for both do the first < space bar > the second

Mike

 

[egun]

Here is an example of how you can make the time update, and still be able to
work in the workbook. It uses the "Application.OnTime" function to schedule
the next call to a subroutine that updates the clock. The example updates
once a second, but you can easily set that to update once a minute.

http://j-walk.com/ss/excel/files/clockchart.htm

 

[egun]

And finally, some more general information on the OnTime function and timers
in Excel:

http://www.cpearson.com/Excel/OnTime.aspx