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

I worked on this answer while Sachin Kohli published his answer. My answer is similar to his solution using VBA, but generalizes the concept a little further.

If you copy this code into a standard module:

Public Sub RecalculateCells(Optional ByVal CellAdress As String = "", _
                            Optional ByVal CellWorkbook As String = "", _
                            Optional ByVal CellWorksheet As String = "", _
                            Optional ByVal updateTimeInterval As Double = 1#, _
                            Optional ByVal updateState As Long = 0)
    Static continue As Boolean

    If updateState = 1 Then continue = True
    If updateState = -1 Then continue = False

    If continue Then
        Workbooks(CellWorkbook).Worksheets(CellWorksheet).Range(CellAdress).Calculate
        Application.OnTime Now() + updateTimeInterval / 86400#, _
            "'RecalculateCells """ & CellAdress & """, """ & CellWorkbook & _
            """, """ & CellWorksheet & """, " & updateTimeInterval & ", 0'"
    End If
End Sub

Public Sub StartUpdatingRange(rng As Range, updateTimeInterval As Double)
    RecalculateCells "B4", rng.Worksheet.Parent.Name, rng.Worksheet.Name, _
        updateTimeInterval, 1
End Sub

Public Sub StopUpdatingRange()
    RecalculateCells , , -1
End Sub

You can recalculate any range at any given time interval. The following example sub will recalculate the cells B6 to B10 once every second.

Sub UpdateB6toB10()
    StartUpdatingRange ThisWorkbook.Worksheets(1).Range("B6:B10"), 1
End Sub

You can stop the recalculation at any given time by calling StopUpdatingRange. You can also call StartUpdatingRange multiple times with different ranges and different update rates. One call to StopUpdatingRange will then stop all of the updates.

Note that if calculation is set to automatic, everything will get updated at the specified rate, not only the selected cells. Therefore, to actually only update the selected cells, set the calculation mode to manual:

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...

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