Real time function

  • Thread starter Thread starter terrylee
  • Start date Start date
T

terrylee

Hi, I am using the function "=now()" to get the date and time. I have also
set the tool > option > calculation to automatic. However, the time is not
changing second by second.
I wonder what setting I need to changed accordingly to activate this real
time function.

Thank you very much
 
Hi Terry.

I believe that the intention of the software is not to do what you are
expecting.

What it will do, however, is if you go out of the spreadsheet that you have
set up and then come back into it, say, 15 minutes later, it is at that point
that the NOW function will get updated with the current time.

If my comments have helped please hit Yes.

Thanks.
 
Just checked MS documentation which states:-

"The results of the TODAY and NOW functions change only when the worksheet
is calculated or when a macro that contains the function is run. Cells that
contain these functions are not updated continuously. The date and time that
are used are taken from the computer's system clock."

This seems to support what I stated in my previous posting.

Please hit Yes if my comments have helped.

Thanks.
 
Hi,

NOW doesn't do that. Right click your sheet tab, voew code. Right click
'ThisWorkbook' and insert module and paste the code below in on the right.

Run the sub StartClock and you get an real time clock in a1 of Sheet1

Run StopClock to stop it

Dim Go As Boolean
Sub StartClock()
Go = True
MyClock
End Sub

Sub MyClock()
If Go Then
Worksheets("Sheet1").Cells(1, 1).Value = Format(Now, "dd mm yyyy hh:mm:ss")
Application.OnTime (Now + TimeSerial(0, 0, 1)), "MyClock"
End If
End Sub

Sub StopClock()
Go = False
End Sub

Mike
 
:
I have also
set the tool > option > calculation to automatic.
However, the time is not changing second by second.
Click to expand...

That should come as no surprise if you read the Help page for the NOW
function. It states: "The NOW function changes only when the worksheet is
calculated or when a macro that contains the function is run. It is not
updated continuously."

You need to use VBA to set up an OnTime event. For details, see:
http://www.cpearson.com/Excel/OnTime.aspx .


----- original message -----
 
Hi, I am using the function "=now()" to get the date and time. I have also
set the tool > option > calculation to automatic. However, the time is not
changing second by second.
I wonder what setting I need to changed accordingly to activate this real
time function.

Thank you very much
Click to expand...

Hi Terry,
I was faced with a similiar problem and made some insights into today
() vs. now() to built a static time stamp, i.e. a time stamp that do
not change after inserted (http://groups.google.com/group/
microsoft.public.excel.worksheet.functions/browse_thread/thread/
d61ee95cf479ab70/ffa7470455227caa?hl=en&q=michael+tarnowski+now()
#ffa7470455227caa)

To built a time stamp use this:

Say your target cell is A1 and to want a static time stamp in B1, put
this formula in B1:

=IF(A1="";"";IF(B1="";NOW();B1))

Then go to Tools|options|calculation and check the iterations
checkbox. When A1 is populated B1 will display a static date+time.
More info:
* http://www.mcgimpsey.com/excel/timestamp.html
* http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/

This is not quite what you are looking for, but maybe you can get some
insights.
HTH,
Cheers Michael


Time stamps, Static
Say your target cell is A1 and to want a static time stamp in B1, put
this formula in B1:
{{{
=IF(A1="";"";IF(B1="";NOW();B1))
}}}
Then Tools|options|calculation and check the iterations checkbox. When
A1 is populated B1 will display a static date.
* http://www.mcgimpsey.com/excel/timestamp.html
* http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/
 
Back
Top