how to create a timer in excel

[Haych]

I would like to create a timer in excel so I can do time and motion studies
quick and easy. It would need a start stop button and each time you stopped
it would start a new time line to continue the process

 

[Jacob Skaria]

Here we go..

Try the below in a new workbook.

1. From menu View>ToolBars>Select Control Toolbox
2. From the control toolbar click 'Command Button'
3. Point to a cell in worksheet and draw the button.
4. Right click the button>Properties and in Properties window enter Caption
as 'Start'
5. Double click the command button and paste the below code within the click
event.

Private Sub CommandButton1_Click()
lngCol = IIf(CommandButton1.Caption = "Stop", 2, 1)
lngRow = ActiveSheet.Cells(Rows.Count, lngCol).End(xlUp).Row + 1
Cells(lngRow, lngCol) = Now()
CommandButton1.Caption = IIf(CommandButton1.Caption = "Stop", "Start", "Stop")
End Sub

6. From workbook toolbar 'control toolbox' click on 'Exit Design mode'
8. Button click and try. This should record the start time and end time in
Col A
and B respectively

PS: If you are new to macros set the Security level to low/medium in
(Tools|Macro|Security).

If this post helps click Yes

 

[Jacob Skaria]

Forgot to mention to format ColA and ColB to display time in date+time in
hh:mm:ss or otherwise the default one would be hh:mm.

I have modified the code as below to reflect the number of seconds between
start and stop in Col C..Test and feedback

Private Sub CommandButton1_Click()
lngCol = IIf(CommandButton1.Caption = "Stop", 2, 1)
lngRow = ActiveSheet.Cells(Rows.Count, lngCol).End(xlUp).Row + 1
Cells(lngRow, lngCol) = Now()
If lngCol = 2 Then Cells(lngRow, 3) = _
DateDiff("s", CDate(Cells(lngRow, 1)), Cells(lngRow, 2))
CommandButton1.Caption = IIf(CommandButton1.Caption = "Stop", "Start", "Stop")
End Sub

If this post helps click Yes