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.
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:
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.
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.
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.
➤Press ALT+F11 on your keyboard. The VBA window will open.
➤ Go to the Insert tab in the VBA window.
➤ From the options available, select Module.
➤ A new module window called “Module 1” will open.
➤ Insert the following VBA code in the module.
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
- Here in the 3rd line, C3 is the cell reference where I want the current time. You use your required one.
➤ Save the workbook as Excel Macro-Enabled Workbook.
➤ Return to your worksheet and press ALT+F8 on your keyboard.
➤ A dialogue box called Macro will open. Select SetTime and click on Run.
➤ You will find the current time being entered in your desired cell and updating every second, just like a running clock.
➤ If you want to stop the clock, again press ALT+F8. This time select Disable.
➤ Then click Run. The clock will stop.
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.