How can i get the NOW function to update every second?

Question

I would like the data in cells B6 B8 B10 to update every second rather than require me to edit the spreadsheet to show the live time. How can I achieve this and what should the formula look like?

Answer 1

This answer provides code to recalculate any range at any given time interval by implementing an Application.OnTime loop.

The example sub UpdateSpecificRange in the following code will recalculate the cells B6 to B10 once every second. To stop the updating, call StopUpdatingSpecificRange or StopUpdatingAll.

'Examples:
Sub UpdateSpecificRange()
    RecalculateRange ThisWorkbook.Worksheets("HOME").Range("B6:B10"), 1
End Sub

Sub StopUpdatingSpecificRange()
    RecalculateRange ThisWorkbook.Worksheets("HOME").Range("B6:B10"), 1, False
End Sub

Sub StopUpdatingAll()
    RecalculateRange stopAll:=True
End Sub

For these examples to work, copy this subroutine into any standard module:

Public Sub RecalculateRange(Optional ByVal Range As Range = Nothing, _
                            Optional ByVal refreshTimeSec As Double = 1#, _
                            Optional ByVal schedule As Boolean = True, _
                            Optional ByVal stopAll As Boolean = False, _
                            Optional ByVal Address As String = "")
    Dim nextExec As Double, macroName As String, wasScheduled As Boolean, task
    Static tasks As Collection: If refreshTimeSec < 1 Then refreshTimeSec = 1#
    If tasks Is Nothing Then Set tasks = New Collection
    If stopAll Then
        For Each task In tasks
            Application.OnTime task(1), task(0), , False: tasks.Remove task(0)
        Next task: Exit Sub
    End If
    If Not Range Is Nothing Then Address = Range.Address(external:=True)
    Address = Replace(Address, "'", "''")
    macroName = "'RecalculateRange , " & Replace(refreshTimeSec, ",", ".") _
                & ", , , """ & Address & """'"
    On Error Resume Next: tasks macroName: wasScheduled = (err.Number = 0)
    On Error GoTo -1: On Error GoTo 0

    If schedule Then
        Application.Range(Replace(Address, "''", "'")).Calculate
        If wasScheduled Then tasks.Remove macroName
        nextExec = DateAdd("s", refreshTimeSec, Now())
        tasks.Add Item:=VBA.Array(macroName, nextExec), Key:=macroName
        Application.OnTime nextExec, macroName
    Else
        If wasScheduled Then
            Application.OnTime tasks(macroName)(1), macroName, , False
            tasks.Remove macroName
        End If
    End If
End Sub

You can also call RecalculateRange multiple times with different ranges and different update rates. You can stop updating them individually aswell be calling RecalculateRange with the same parameters you first called RecalculateRange including the parameter schedule:=False. You can also stop updating all of them at once by calling RecalculateRange with the optional parameter StopAll = True as follows: RecalculateRange StopAll:=True

Since Application.OnTime can reopen the workbook if it was closed, we have to stop all the scheduled range updates before the workbook is closed. This can be done automaticaly by leveraging the workbook BeforeClose event. To do this, you have to paste the following code into the ThisWorkbook code module:

Option Explicit 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RecalculateRange StopAll:=True
End Sub

Starting the updating automatically when the workbook is opened can also be done in the ThisWorkbook code module, by leveraging the workbook Open event. To do this, you can paste the following into the ThisWorkbook code module:

Private Sub Workbook_Open()
    RecalculateRange ThisWorkbook.Worksheets("HOME").Range("B6:B10"), 1
End Sub

To open the ThisWorkbook code module, double click it in the VBA Project-Explorer as highlighted in the following screenshot:

Answer 2

Solved with VBA: -> Update clock every Second

Sub clock_timer()
    Sheets("Sheet1").Range("B6").Value = Now
    Application.OnTime Now + TimeValue("00:00:01"), "clock_timer"
End Sub

Solved without VBA: -> Update clock every Minute

Using Query & Connections, follow these steps;

Step 1: Select "B5 to B10" & click on Insert -> Table
Step 2: Click on Data -> From Table/Range
Step 3: A new pop up will open with Query -> Click on Close & Load
Step 4: Right click on newly created Table under section "Queries & 
        Connections" & go to Properties
Step 5: Edit Refresh option to "Every 1 Minute"

Another Solution without VBA: By downloading the excel's extensions "XLTools"

Hope it Helps...

Answer 3

Its actually a really simple Answer in VBA.

Sub Macro1()

x = Application.Ontime Now() + Timevalue("00:00:01"), Macro1 
Range("A1").Value = x 

End Sub

This macro will update a clock in Cell A1 on your sheet every second.

A suggestion would be to use "get data from web" tab and call different timezones time from internet in excel cells..

What you have to do is.

Select Data Tab > Get Data From Web > Enter Dateandtime.com in browser that opens within excel.

Once Data is there the date and time from the cell is dynamic. Just hit data and connections. Set the Refresh data every 1 minute or second. This will give you a live clock from the internet. You can also get time from different timezones.

An example would be www.Dateandtime.com