PC Review


Reply
Thread Tools Rating: Thread Rating: 3 votes, 3.67 average.

how to create a timer in excel

 
 
Haych
Guest
Posts: n/a
 
      30th Jun 2009
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

-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com
 
Reply With Quote
 
 
 
 
Jacob Skaria
Guest
Posts: n/a
 
      30th Jun 2009
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


"Haych" wrote:

> 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

 
Reply With Quote
 
 
 
 
Jacob Skaria
Guest
Posts: n/a
 
      30th Jun 2009

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

> 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
>
>
> "Haych" wrote:
>
> > 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

 
Reply With Quote
 
New Member
Join Date: Sep 2009
Posts: 1
 
      7th Sep 2009
Hey Jacob

This is great, just what I'm looking for to measure work time for various roles in my org. Is there any way to add colour to the Button eg. When you the button is on "Start" it is displayed as Green and when on "stop" it is red????

Quote:
Originally Posted by 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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

> 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
>
>
> "Haych" wrote:
>
> > 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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stopping a Timer / Running a timer simultaneously on Excel =?Utf-8?B?UGF1bDIz?= Microsoft Excel Misc 1 10th Mar 2006 01:08 PM
System.Timers.Timer bug : timer fires immediately/continously felix Microsoft Dot NET Framework 2 25th Apr 2005 06:48 PM
Does calling Timer.Dispose() prevent the timer from firing ? Anuj Agarwal Microsoft Dot NET Framework 2 26th Oct 2004 07:00 PM
Timer control vs Timer class VJ Microsoft Dot NET Framework 2 18th Apr 2004 05:55 PM
System.Timers.Timer vs. System.Threading.Timer Kelsang Wangchuk Microsoft ASP .NET 0 31st Jul 2003 05:28 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:26 PM.