How to create stopwatch in Excel worksheet?
If there is a stopwatch in your worksheet, you will know the time to finish a job. How can you create a stopwatch in a sheet with a Start, Stop, and Reset Button as following screenshot shown? If you click the Start button, it will start time, if you click Stop button, it will stop time, and the Reset button can help you to reset time. This article, I will talk about how to create a simple and handy stopwatch in Excel.
To create a stopwatch with three buttons, Start, Stop and Reset, please do with the following steps one by one.
Firstly, insert three command buttons.
1. Click Developer > Insert > Command Button, see screenshot:
2. Then drag the mouse to draw a button, after inserting the button, you can change the button caption, please click Developer > Properties, in the Properties dialog, enter the new caption “Start” for this button in the textbox beside the Caption, see screenshots:
3. Repeat the above two steps to insert other two buttons and caption them as “Stop” and “Reset”, see screenshot:
4. After inserting the buttons, you should exit the design mode by clicking Developer > Design Mode.
Secondly, create a VBA code.
5. And then, right click the current worksheet tab, and choose View Code, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the Module:
VBA code: Create stopwatch:
Public StopIt As Boolean Public ResetIt As Boolean Public LastTime Private Sub CommandButton1_Click() Dim StartTime, FinishTime, TotalTime, PauseTime StopIt = False ResetIt = False If Range("C2") = 0 Then StartTime = Timer PauseTime = 0 LastTime = 0 Else StartTime = 0 PauseTime = Timer End If StartIt: DoEvents If StopIt = True Then LastTime = TotalTime Exit Sub Else FinishTime = Timer TotalTime = FinishTime - StartTime + LastTime - PauseTime TTime = TotalTime * 100 HM = TTime Mod 100 TTime = TTime \ 100 hh = TTime \ 3600 TTime = TTime Mod 3600 MM = TTime \ 60 SS = TTime Mod 60 Range("C2").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00") If ResetIt = True Then Range("C2") = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00") LastTime = 0 PauseTime = 0 End End If GoTo StartIt End If End Sub Private Sub CommandButton2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) StopIt = True End Sub Private Sub CommandButton3_Click() Range("C2").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00") LastTime = 0 ResetIt = True End Sub
Note: In the above code, C2 is the cell where the stopwatch time will be inserted, and the CommandButton1, CommandButton2, CommandButton3 are the button names, you can view the exact button name from the Name Box, see screenshot:
Thirdly, format the stopwatch time cell.
6. Then you should format the time cell as Text format, and you can change the cell size, font, font color, background, etc. as you need, see screenshot:
7. After finishing above steps, from now on, when you click Start button, the time will be start now, and click Stop button, it will stop time, the Reset button will reset the time, see screenshot:
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agois 'timer' a variable here? dont see its relevance in the code.
- To post as a guest, your comment is unpublished.· 4 months agoHow about adding "+10s" & "2x Speed" feature in this code?
- To post as a guest, your comment is unpublished.· 4 months agoDone all the steps but clicking the start button give error message "Cannot run the macro "'file.xlsm'!CommandButton1". The macro may not be available in this workbook or all macros may be disabled.". All macros have been enabled, the macro is in Module1. NOTE: I'm running Office 2019 for Mac so that could be a problem....
- To post as a guest, your comment is unpublished.· 11 months agoHI. I have created the stopwatch, but need to be able to have the various intervals between the start and stop to be saved to be able to reflect them as a time and motion study. Any tips on how to do that?
- To post as a guest, your comment is unpublished.· 1 years agoThank for your tip, It was running smoothly but when I input any data in this sheet It's stopped running, We need it keep running awhile input data How we can fix this problem?
- To post as a guest, your comment is unpublished.· 1 years agoHello, Nhoeun,
May be there in not a good way for solving your problem. When you edit a cell, it will interrupt the currently executed macro.
If you have any other good method, please comment here!
- To post as a guest, your comment is unpublished.· 1 years agoHi, I'm using this stopper but quite often the numbers in the display cell ("C2") start fricking out and go off - they show minus signs, the digits are incorret and the count is lost. It usually happens after about an hour or more but sometimes even before an hour goes by. Is this a common bug? and if yes, is there a way to fix it? I did remove the .00 (i.e. the miliseconds) from the code by simply erasing the: & "." & Format(0, "00") line wherever it appears - does that have to do anything with this problem?
- To post as a guest, your comment is unpublished.· 2 years agoIs there any way to have the timer reset the count to zero but still keep the timer running? Ie. When I press Reset, I don't want to have to press Start again for the time to tick up.
- To post as a guest, your comment is unpublished.· 2 years agoIs it possible to automatically stop the timer when screen locks and start the timer automatically when we unlock the computer?
- To post as a guest, your comment is unpublished.· 2 years agoGreat, but is it possible to run 2 clocks in the same sheet? have made a second clock with new start, stop and reset buttom, but when starting the second clock the first one stops....
- To post as a guest, your comment is unpublished.· 2 years agosame here can you able to find cure of this
- To post as a guest, your comment is unpublished.· 2 years agoHello, friends,
This method is only applied to one set of buttons in a worksheet.
If there are other good ideas, please comment here.
- To post as a guest, your comment is unpublished.· 2 years agoIs there any way to get the timer to count up or down to a specific time? I want to keep the start, stop, and reset buttons, but I need it to count up to one minute one second only, once I press start. Or count down from one minute one second to zero (01:01). Thanks for your help!
- To post as a guest, your comment is unpublished.· 2 years agoHi, Melissa,
if you need to create a countdown timer in worksheet, may may the the following article can help you!
- To post as a guest, your comment is unpublished.· 2 years agohello,
I did see that article, but i need it to look exactly like the stop watch on this page, but i need it to only count down from one minute one second. I need it to show in stopwatch form, but work as a count down. Any further advice?
thank you for replying!
- To post as a guest, your comment is unpublished.· 2 years agoIt is for a scoreboard that we designed in excel.
- To post as a guest, your comment is unpublished.· 2 years agoTry XNote Stopwatch.
- To post as a guest, your comment is unpublished.· 3 years agoHave tried to create the stop watch using the same codes in Forms. The timer starts perfectly fine but does not stop . Please suggest how to replicate the same in forms
- To post as a guest, your comment is unpublished.· 3 years agoThis was a great help and very easy to follow the steps.
- To post as a guest, your comment is unpublished.· 3 years agoThank you for this post, it was very helpful. However, is there a way to tweak the code to make it so the timer does not stop when a cell is being edited? Ideally I would like to hit "Start," make a number of changes to a range of cells on the same tab, and then hit "Stop" for the total time elapsed. Any advice on this would be greatly appreciated!
- To post as a guest, your comment is unpublished.· 19 days agodid you ever figure this one out? I too need to keep the timer running while other changes are being made in the same spreadsheet. thanks. Mike