How to create Activity Tracker / Timer

If you'd like to keep in Excel the record of your common daily tasks, in terms of disciplined use of your valuable time, then the solution I'm providing here can be helpful. It makes easy recording of duration of any routine daily activities and provides basis for further analysis of any kind.

This is how it looks like in an exemplary edition:

Time, and time again

When you organize your work, in Excel or elsewhere, at some point you may want to keep track of your activities in a worksheet/workbook. Here you'll find two helpful ways of recording time spent on whatever you do. The first one allows you to record just a point in time, like a stamp of current time. The second one shows you elapsed time, difference between starting of some activity and then stopping it.

Time stamp

To create a time stamp similar to this one: 

 

• Insert a Command Button in your worksheet, by clicking Developer > Insert > Command Button (ActiveX Control).
• Right-click the button and select Properties to change its Caption to "Time Stamp"; change any other properties as needed.
  
• Right click the Command Button again, then click View Code from the context menu. When VBA window pops up, enter the following VBA script:

Sub TimeStamp()
    ActiveCell.Value = Time
    ActiveCell.NumberFormat = "hh:mm:ss AM/PM"
End Sub

• Now press the Alt + Q keys simultaneously to close the VBA window.

Select a cell where you want to place the current time stamp, then click the Command Button. You've got your stamp placed in the selected cell.

Time watcher

To create a timer (to get elapsed time), kind of time watcher, similar to this one:

• Select some range of cells for your timer (3 rows by 3 columns).
• Insert two Command Buttons (side by side), by clicking Developer > Insert > Command Button (ActiveX Control). Format them to your liking.
• Right-click one of them and select Properties to change its caption to "Start"; change any other properties, as needed. Repeat that for the second button and change its caption to "Stop".
• Right click any of the Command Buttons and click View Code from the context menu. When VBA window pops up, enter the following VBA scripts:

Private Sub StartNow()
    Dim Rcell As Range     'Replace "C25" below with your selected range   
    Range("C25").NumberFormat = "dd/mm/yyyy  hh:mm:ss"
    Set Rcell = Range("C25")
    Rcell.Value = Now()
End Sub

Private Sub StopNow()
    Dim Rcell As Range    'Replace "C25","D25","E25" below with your selected ranges
    Range("D25").NumberFormat = "dd/mm/yyyy  hh:mm:ss"
    Set Rcell = Range("D25")
    Rcell.Value = Now()
    Range("E25").Select
    Selection.NumberFormat = "d"" day(s) &"" hh:mm:ss"
    Selection.Value = Range("D25").Value - Range("C25").Value
End Sub

Private Sub CommandButton1_Click()    'Replace "1" with actual number, if necessary
StartNow
End Sub

Private Sub CommandButton2_Click()    'Replace "2" with actual number, if necessary
StopNow
End Sub

• Now press the Alt + Q keys simultaneously to close the VBA window.
  
• In your worksheet, format (optional) the cells with "Timer" and "Elapsed Time" text.

That's it. Now you can use your time watcher as needed. Click on "Start" button to start timing. Click on "Stop" button to end timing, whenever needed (even after days, weeks, months). Elapsed time will be displayed in cell E25 (in this example), including the number of days passed.

You can also show elapsed time without any VBA coding, by using a simple formula. However, prior to that you need to format cells A1:C1 according to your needs (any custom format) and enter Start and Stop times in required format. Assuming that you store Start time in A1, and Stop time in B1 (which can be entered with help of formula =NOW() ), enter in C1 the formula: =B1-A1. This will show the elapsed time.