Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Start/Stop Timer For Hours Worked To Include Break Time

  1. #1
    Join Date
    16th August 2007
    Posts
    29

    Start/Stop Timer For Hours Worked To Include Break Time

    Hi,

    I have a timesheet where user updates start and end time for various tasks.

    I have placed a time capture button in the excel sheet (which is simply a macro saying =now() function)

    The user clicks it before starting and after finishing the task. The start and end times are captured in adjacent cells.

    If the user starts the work, and goes on a lunch break say for 20 min, comes back finishes the task and captures end time, the time difference will not consider break time which is non productive.

    How can I incorporate something like 'pause' option so that before he goes for lunch he can temporarily pause the time.

    Thanks,

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd September 2003
    Location
    York
    Posts
    880

    Re: Multiple Condition Time Difference

    Hi krishr1,

    You could add another button marked pause which when pressed records the time and changes the text on the button to "resume task". When it is pressed again it records the time, subtracts the time recorded the first time it was pressed, adds the difference in minutes to a variable which is used to subtract non productive time when the task is finished. The second tme the button is pressed it would also change the text back to pause.

    You should also clean up the variables when you start and stop tasks.

    You could also store the times in cells on a hidden sheet and then cope with someone pausing the task and saving / closing the workbook overnight (you would need a cell to record the status the workbook was closed in (i.e. paused or not)

    Hope this helps.

    John

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    16th August 2007
    Posts
    29

    Re: Multiple Condition Time Difference

    Thanks John!

    This is exactly what I had in mind.

    The only problem is execution. I am bad with VB. It will be helpful if you can give me the code.

    Many thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd September 2003
    Location
    York
    Posts
    880

    Re: Multiple Condition Time Difference

    Hi,

    What type of button are you using at the moment? Are they in a form or on the sheet?

    Can you post the code you have already?

    John

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    16th August 2007
    Posts
    29

    Re: Multiple Condition Time Difference

    The button im referring to is just a small rectangle with text "capture time". The rectangle is drawn is from the 'drawing' tool in excel. I have not used any forms here.

    If you click the rectangle, the macro that runs is:

    VB:
    Sub time() 
        activecell.value = now() 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd September 2003
    Location
    York
    Posts
    880

    Re: Start/Stop Timer For Hours Worked To Include Break Time

    Hi,

    Insert a new sheet called "Hidden" and use the following code...
    VB:
    Sub Macro1() 
        Sheets("Hidden").Cells(1, 1).Value = Now() 
        ActiveSheet.Shapes("Rectangle 1").Select 
        Selection.Characters.Text = "Pause" 
        Selection.OnAction = "Macro2" 
        ActiveCell.Select 
    End Sub 
    Sub Macro2() 
        Sheets("Hidden").Cells(2, 1).Value = Now() 
        ActiveSheet.Shapes("Rectangle 1").Select 
        Selection.Characters.Text = "Resume" 
        Selection.OnAction = "Macro1" 
        ActiveCell.Select 
    End Sub 
    
    
    Look at what this does step by step and you will see the times being recorded on the sheet called hidden.

    The code you use to "stop" the task will take the activecell away from now() I assume, all you have to do is also take away the difference (i.e. the paused time) and clear the two cells so your next task doesn't use the same date.

    John

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    16th August 2007
    Posts
    29

    Re: Start/Stop Timer For Hours Worked To Include Break Time

    Sorry if I am asking you some basic doubts.

    I added a worksheet and renamed it as 'hidden'

    I pasted your code in a module.

    I created a new rectangle (and added text 'pause') in sheet1 (where i capture start and end time) and assigned macro - macro1

    i captured the start time and then clicked on pause button.

    error is: the item with the specified name wasnt found

    may be it is not recognizing the code 'rectangle 1'

    thanks again so far

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    23rd September 2003
    Location
    York
    Posts
    880

    Re: Start/Stop Timer For Hours Worked To Include Break Time

    Yup,

    You need to find out what Excel has called your rectangle.

    Select the rectangle and look at the top left of your screen it shold say Rectangle X If you were in cell A1 it would say A1 in the same place.

    John

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    16th August 2007
    Posts
    29

    Re: Start/Stop Timer For Hours Worked To Include Break Time

    I am not getting the desired result

    lets say when i click capture button it shows 10:00AM in cell B10

    I work for 15 minutes. Click on Pause button and leave desk. Come back after 10 minutes. Click on resume button. work for 20 minutes. goto cell B11. click on capture button. B11 should show 10:35AM.

    This is not happening.

    Also when i click on pause button, name changes to resume. until i click on resume, it should not allow me to use capture button

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    23rd September 2003
    Location
    York
    Posts
    880

    Re: Start/Stop Timer For Hours Worked To Include Break Time

    Hi,

    Try writing down logically what you think is supposed to happen when you press each button...

    Start should record the start time

    Stop shoud record the stop time and display the stop time minus the start time (NB1)

    Pause should record a time

    Resume should record a time and save the time elapsed since paused

    The above are without any extras to disable buttons (i.e. assign a macro to display a warning and grey out the text) or to record multiple pauses. Get the basics right first.

    Now step through your code and see what it is actually doing.

    Try not to get flustered, just take it one step at a time, most, if not all the code for what you are doing can be found using record macros.

    John

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: February 14th, 2008, 17:06
  2. Calculate Hours Worked After Specific Time
    By needhelp2008 in forum EXCEL HELP
    Replies: 11
    Last Post: February 10th, 2008, 14:49
  3. Automate Macro Start On Time & Stop At Later Time
    By Greg108 in forum EXCEL HELP
    Replies: 4
    Last Post: December 26th, 2007, 10:55
  4. Replies: 3
    Last Post: September 14th, 2007, 08:48
  5. Replies: 11
    Last Post: May 8th, 2006, 13:48

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno