Start and Stop Timer to Track Time

[AmberLynn]

I am looking to create a spreadsheet that will allow me to track my time by day and cost center. Each cost center will have a stop and start timer for each day of the week and can be started and stopped multiple times.

It will look something like this....

Cost Ctr 01/04/16 01/05/16 01/06/16 01/07/16 01/08/16 Monday Tuesday Wednesday Thursday Friday
11111111 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 start stop start stop start stop start stop start stop
22222222 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 start stop start stop start stop start stop start stop
33333333 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 start stop start stop start stop start stop start stop
44444444 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 start stop start stop start stop start stop start stop
55555555 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 start stop start stop start stop start stop start stop


If anyone knows how I could create something like this, I would really appreciate you!

Thanks in advance!

 

[oldbrewer]

[TABLE="width: 855"]
<colgroup><col><col span="12"></colgroup><tbody>[TR]
[TD][/TD]
[TD]start[/TD]
[TD]stop[/TD]
[TD]start[/TD]
[TD]stop[/TD]
[TD]start[/TD]
[TD]stop[/TD]
[TD]start[/TD]
[TD]stop[/TD]
[TD]start[/TD]
[TD]stop[/TD]
[TD]tot[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]costcentre1[/TD]
[TD="align: right"]05:43[/TD]
[TD="align: right"]06:11[/TD]
[TD="align: right"]08:15[/TD]
[TD="align: right"]08:45[/TD]
[TD="align: right"]11:10[/TD]
[TD="align: right"]12:35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02:23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]00:28[/TD]
[TD][/TD]
[TD="align: right"]00:30[/TD]
[TD][/TD]
[TD="align: right"]01:25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02:23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]rows 3 and 4 are for checking purposes only[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]formula giving 02:23 in L2 is[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]=C2+E2+G2+I2+K2-B2-D2-F2-H2-J2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]if you added two more start and stop times it would still work[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]however if your data is costcentre1 09:23,10:15 costcentre3 10:17, 10:19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]this approach will not help[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mackers]

Hi

Each set of start and stop times could have an additional column that was the total time during said period (end time minus start time). Then you could simply add all of these up - I might be missing something but I think this is easier and clearer than trying to add all of them up in one formula.

Hope that helps

Mackers

 

[oldbrewer]

Mackers - it all depends on how the data is recorded eg is it an automated process - will wait for comments from OP

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I know Excel encourages you to combine data input, storage and presentation all on one sheet, but in this case it might be easier to go for the database approach where you keep those three things separate.

So I would go for one sheet that you can use to specify the cost centre you are working for. I would not record stop times because that will be the same as the next start time - except when you take a break for lunch etc. The workaround would be to have a "cost centre" for non-working time. You could have more than one category if you wanted to analyse non-working times as well.

So my first sheet would look like this:

Excel 2013
	A	B	C	D	E	F
1	Cost Ctr
2	Not Working	11111111	22222222	33333333	44444444	55555555
CC Logging

A macro for this sheet would detect any right-clicks on the cost centre numbers and would log the details and change the colour to orange so that you can easily see the one you are supposed to be working on.
The macro for that worksheet is:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Const CostCtrRow As Long = 2
    Dim nr As Long
    If Target.Count > 1 Then Exit Sub
    If Target.Row = CostCtrRow Then
        With ThisWorkbook.Worksheets("Data")
            nr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            .Cells(nr, "A") = Target.Value
            .Cells(nr, "B") = Now
            .Cells(nr, "C") = Date
        End With
        Rows(CostCtrRow).EntireRow.Interior.ColorIndex = xlColorIndexNone
        Target.Interior.ColorIndex = 46
        Cancel = True
    End If
End Sub

The second sheet I called Data and it will look like this:

Excel 2013
	A	B	C
1	Cost Ctr	DateTime	Date
2	11111111	2016/01/06 16:38:56	06/01/2016
3	44444444	2016/01/06 16:39:42	06/01/2016
4	Not Working	2016/01/06 16:41:42	06/01/2016
5	55555555	2016/01/06 16:41:45	06/01/2016
Data

Now you can think how you would like to process that data into any report you might need.

 

[AmberLynn]

Thank you RickXL. This will work perfectly for what I am trying to do. Unfortunately, I cannot get it to work as you have. I created the 2 Sheets Data and CC Logging and copied the code as you have it, but when I right click on a cost center, nothing is executed. Did I miss a step? Also, just to note, I am using excel 2010. Not sure if that would make a difference.

 

[AmberLynn]

Thank you oldbrewer. I was looking for something so that I could avoid having to type the times in manually. That is why I was thinking a start/stop timer.

 

[RickXL]

Thank you RickXL. This will work perfectly for what I am trying to do. Unfortunately, I cannot get it to work as you have. I created the 2 Sheets Data and CC Logging and copied the code as you have it, but when I right click on a cost center, nothing is executed. Did I miss a step? Also, just to note, I am using excel 2010. Not sure if that would make a difference.

You need to paste the code into the code module for the CC Logging sheet and not a standard Module.