How to Auto-Update Current Time in Excel (With Formula and VBA)

While working in Excel, we often have to enter the current date and time in a cell for various purposes and auto-update them. Today I’ll show you how you can auto-update the current date in a cell in Excel.


Download Practice Workbook


2 Methods to Auto-Update Current Time in Excel

Here we’ve got a workbook where we need to enter the current time in cell C3.

Data Set to Auto-Update Current Time in Excel

Our objective is to enter the current time in such a way that it updates automatically.


1. Use the NOW Function to Auto Update Current Time in Excel

You can use the NOW function of Excel to auto-update the current time in Excel.

Select the specific cell and enter this formula:

=NOW()

Now Function to Auto-Update Current Time in Excel

It will enter the current time on the cell taken from your computer.

Each time you make any change in your workbook, the formula will recalculate automatically and update the current time.

If you want to change the format of the time, select the cell and go to the Home > More Number Formats tool under the section called Numbers.

Changing the Current Time Format in Excel

Click it. It will open the Format Cells dialogue box.

Select Custom from the left panel. Then select your desired format from the formats available in the right box.

Format Cells Box to Change Current Time Format in Excel


2. Run VBA Codes to Auto Update Current Time in Excel

The NOW function described above does work, but each time you need to auto-update the time, you have to make any change in your worksheet.

You can use a VBA code instead that will auto-update the current time every second.

You won’t have to do anything.

Step 1:

Press ALT+F11 on your keyboard. The VBA window will open.

VBA Code to Auto Update Current Time in Excel

Step 2:

Go to the Insert tab in the VBA window. 

From the options available, select Module.

Inserting Module to Auto Update Current Time in Excel

Step 3:

A new module window called “Module 1” will open. 

Insert the following VBA code in the module.

Code:

Dim SchedRecalc As Date
Sub Recalc()
With Sheet1.Range("C3")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub

Note:

  • Here in the 3rd line, C3 is the cell reference where I want the current time. You use your required one.

VBA Code to Auto-Update Current Time in Excel

Step 4:

Save the workbook as Excel Macro-Enabled Workbook.

Saving Macro Enabled Workbook in Excel

Step 5:

Return to your worksheet and press ALT+F8 on your keyboard.

A dialogue box called Macro will open. Select SetTime and click on Run.

Running Macro to Auto-Update Current Time in Excel

Step 6:

You will find the current time being entered in your desired cell and updating every second, just like a running clock.

Auto Update Current Time in Excel

Step 7:

If you want to stop the clock, again press ALT+F8. This time select Disable.

Then click Run. The clock will stop.

Stopping Auto Updated Current Time in Excel


Conclusion

Using these methods, you can auto-update the current time in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.


Related Readings

How to Insert Dates in Excel Automatically (3 Simple Tricks)

How to Enter Time in Excel (5 Methods)

Current Date Formula in Excel

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo