Automatically Update Time and Date

[Tatsumaru]

How can I make cells I10 and I11, with =TODAY() and =NOW() in them, respectively. I have set up =NOW() to only show the time. I need this time to be updated every minute, is there a way for excel to automatically do that. Perhaps with auto_open macro.

Thanks in advance

 

[AgentSmith]

You can use Application.OnTime to execute a macro after a scheduled delay. So you might want to use something like this in your Workbook code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime NextScheduledUpdate, "UpdateDateTime", , False
End Sub

Private Sub Workbook_Open()
    UpdateDateTime
End Sub

Then in a separate module:

Public NextScheduledUpdate As Variant

Sub UpdateDateTime()
    Range("MyDate").Calculate
    Range("MyTime").Calculate
    
    NextScheduledUpdate = Now + TimeSerial(0, 1, 0)
    Application.OnTime NextScheduledUpdate, "UpdateDateTime"
End Sub

 

[Jaafar Tribak]

Hi Tatsumaru,

For an accurate time update you can try the following codes:

Place this code in the ThisWorkBook Module:


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">Dim</SPAN> CalcTime <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    CalcTime = Format(WorksheetFunction. _
    Ceiling(Timer, 60) / 86400, "long time")
    Application.OnTime TimeValue(CalcTime), "UpdateTime"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


And this code in a Standard Module :


<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UpdateTime()
    Range("I11").Calculate
    Application.OnTime Time + _
    TimeSerial(0, 1, 0), "UpdateTime"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Save,close and reopen the workbook and you are done.

Regards.

 

[Tatsumaru]

thank you both of you. I have, with the help of my friend (and you two!), now sorted the updating time problem.

Thanks again