Announcement

Collapse
No announcement yet.

Start/Stop Timer For Hours Worked To Include Break Time

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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,

  • #2
    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

    Comment


    • #3
      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.

      Comment


      • #4
        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

        Comment


        • #5
          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:

          Code:
          sub time()
          activecell.value = now()
          end sub

          Comment


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

            Hi,

            Insert a new sheet called "Hidden" and use the following code...
            Code:
            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

            Comment


            • #7
              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

              Comment


              • #8
                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

                Comment


                • #9
                  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

                  Comment


                  • #10


                    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

                    Comment

                    Working...
                    X