Can you put a running clock into excel?

nightracer

Board Regular
Joined
May 17, 2004
Messages
147
Office Version
  1. 365
Hi all

I have a process that is due to start at say 3.00pm and I need to be able to show the elapsed time that the process has been running.

I've used the formula =NOW()-"15:00:00" which gives me the answer but it only updates when you hit return or enter text.

Is there a way to display a running clock/stopwatch?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
The following macros will place today’s date and a running time in cells C3 and C4 respectively. The last macro manually stops the clock.

Standard module.
Code:
Dim SchedRecalc As Date
Sub Recalc()
Range("C3").Value = Format(Now, "dd-mmm-yy")
Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")

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

ThisWorkbook module
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Disable
End Sub
HTH


Mike
 
Upvote 0
For clocks:

http://www.cpearson.com/excel/download.htm
http://www.j-walk.com/ss/excel/files/general.htm

Search the code in them for the term "OnTime". (Or search here for that term.)

If you are running a loop in a VB procedure and simply want to periodically update the user, you can simply use Application.Statusbar = xxx. You might search for the term "Progress Indicator" here for more ideas.

{Edit}I see that Mike beat me to the punch - and with a very nice answer.{EndEdit}

HTH
 
Upvote 0
I just realized that unless the “ReCalc” macro explicitly refers to the active workbook, the date and time is place in all open workbooks. So, change the first macro to the following:
Code:
Dim SchedRecalc As Date
Sub Recalc()
Dim wbk As Workbook
Dim ws As Worksheet

Set wbk = ThisWorkbook
Set ws = wbk.Sheets("Sheet1")

ws.Range("C3").Value = Format(Now, "dd-mmm-yy")
ws.Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")

Call SetTime
End Sub
Regards,

Mike
 
Upvote 0
Thanks Mike/Greg

Can these be used with formula's in any way to show elapsed time rather than just time of day?
 
Upvote 0
Sure, using Mike's great example - go to cell B4 and hit Ctrl+: to enter the current time. Then go to D4 and put in the formula =C4-B4 and format it to hh:mm:ss
 
Upvote 0
Mike, thanks again

When I try to paste your macro into the vb and run it I get the message 'can't execute code in break mode'.

Any ideas what this might mean?
 
Upvote 0
“can't execute code in break mode” normally means that you have attempted to run a macro that has previously stop running because of some error. To reset the macro:

In the VBA editor:
Go to the menu item “Run”
Click “Reset”

If the macro does not run on your system, what error message is displayed?

Regards,

Mike
 
Upvote 0
I am trying to run this from a form button, but am a newbie to macros so please forgive any novicy mistakes!

Here are the steps I've taken:

1 Insert a form button onto the worksheet
2 in Assign Macro I've clicked 'new'
3 In visual basic i've copied in the above to show:

Sub Button1_Click()
Dim SchedRecalc As Date
Sub Recalc()
Dim wbk As Workbook
Dim ws As Worksheet

Set wbk = ThisWorkbook
Set ws = wbk.Sheets("Sheet1")

ws.Range("C3").Value = Format(Now, "dd-mmm-yy")
ws.Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")

Call SetTime
End Sub

4 I've gone back to the worksheet and clicked the button, but get the message:

Compile Error: Expected End Sub with 'Dim' highlighted on the second line: Dim SchedRecalc As Date

Anyone know what I've done wrong? Do I need to use different settings?
 
Upvote 0
Thanks everyone, now working with:

Sub Button1_Click()
Dim SchedRecalc As Date
Sub Recalc()
Dim wbk As Workbook
Dim ws As Worksheet

Set wbk = ThisWorkbook
Set ws = wbk.Sheets("Sheet1")

ws.Range("C3").Value = Format(Now, "dd-mmm-yy")
ws.Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")

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

Thanks again
nightracer
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top