Track Time in Excel with VBA

Track Time in Excel with VBA

Last week, we saw the steps for entering project start and stop times in Excel, by using keyboard shortcuts. Then, Excel formulas can calculate the total project time, based on the start and stop times.


timetrack08

One problem with using the keyboard shortcut to enter the current time is that it doesn’t include the seconds in the time. All the times are entered as 00 seconds, as you can see in the screen shot below.

timetrack01

So, if you’re keeping track of very short tasks, or need precision to the second, the keyboard shortcut for entering time won’t help you.

Enter Current Time with Excel VBA

Instead of using the default shortcut for entering the current time, you can use Excel VBA to enter the time.

For example, you can select a cell on the worksheet, and run the following macro, to enter the current time in the active cell. The time will automatically include hours, minutes, and seconds.

Sub EnterTime()
    ActiveCell.Value = Time
End Sub

timetrack10

Enter the Current Time with a Double-Click

It’s not very convenient to manually run a macro when you want to enter the current time. So, to make it easier, you can use Event code, and automatically run the macro when a cell is double-clicked.

In this example, the start and stop times will be entered in a range named TimeEntry. That range is selected in the screen shot below – cells B2:C4

timetrack11

The following code is stored on the code module for the worksheet where you want to enter the times.

The code only runs if you double-click an empty cell (.Value = “”) in the TimeEntry range.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Dim rng As Range
Set rng = Range("TimeEntry")
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
    Cancel = True 'stop the edit mode
    With Target
        If .Value = "" Then
            .Value = Time
        End If
    End With
End If
Application.EnableEvents = True
End Sub

After you put this procedure onto the worksheet module, double-click in an empty Start or Stop time cell. The current time, including seconds, is automatically entered.

Move to the Next Cell

As a refinement to the previous procedure, you can add a line that moves you to the next cell, after entering the time. In the following revised code, the cell that is one column to the right is activated, after the time is entered.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Dim rng As Range
Set rng = Range("TimeEntry")
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
    Cancel = True 'stop the edit mode
    With Target
        If .Value = "" Then
            .Value = Time
            .Offset(0, 1).Activate
        End If
    End With
End If
Application.EnableEvents = True
End Sub

Download the Sample File

To see the time tracking macro, and the formulas, you can download the sample Excel Time Tracking file from the Contextures website.

In the Functions section, look for FN0021Track Project Time
______________

30 thoughts on “Track Time in Excel with VBA”

  1. My preference for putting in the time is the NOW() function since it does the day also, so, if you have a late night, your resulting time won’t be off.

  2. Hi there,
    Your double-click sub really improved my own time tracking sheet.
    I got one question, though: Say I want to enter the date, too. In a different column, a different range. How do I do that? Excel doesn’t let you name two subs with a double-click event.
    Your help would be greatly apprreciated. Thanks again for the nice inspiration.
    Best,
    Andy

    1. @Andy, thanks, glad it helped! To have different entries for a double-click, you could check the column of the target cell. For example, if the date will be entered in the first column of the named range, use something like this:

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      On Error Resume Next
      Dim rng As Range
      Dim lCol As Long
      Set rng = Range("TimeEntry")
      lCol = Target.Column
      If Target.Count > 1 Then Exit Sub
      If Not Intersect(Target, rng) Is Nothing Then
          Application.EnableEvents = False
          With Target
              If .Value = "" Then
                  If lCol = rng.Columns(1).Column Then
                      .Value = Date
                  Else
                      .Value = Time()
                  End If
                  .Offset(0, 1).Activate
              End If
          End With
      End If
      Application.EnableEvents = True
      End Sub
      1. Dear Debra
        Thanks, that was extremely helpful. As it is, I am relatively new to VBA and don’t fully understand your code. I know enough to manipulate it to serve my means, but that’s about it. I couldn’t have altered it to account for the date as well on my own.
        Thank you so much, I shall be following your blog from now on 😉
        Andy

  3. If you look at the function call, you’ll notice the Cancel parm. This tells excel not to go into edit mode (if you’re nutty enough to use edit in cell!).
    You may want to add a line:
    …bunch of code…
    Application.EnableEvents = False
    cancel = true ‘stop the edit mode
    With Target
    …more code…
    And I don’t know why you use time(). Why not just:
    .Value = Time

  4. Thanks, Deb.
    I had a less than sign, vbg, greater than sign after the time() note, but those HTML thingies are too much for me to remember.

  5. Just for those who are keyboard centric, could use the following keystrokes:
    CTRL-: (colon) will insert the current date
    CTRL-; (semi-colon or rather CTRL+SHIFT-{colon}) will insert the current time.
    So IF you need the current date & time in one cell, you would type:
    CTRL-: {space} CTRL+;
    Easy-peasy!
    🙂

  6. I finished my time card add-in recently. Go try it out if you are interested. It is free. There are still more things I need to work on but this is the initial release.
    (Hope it’s OK that I post this here Debra 🙂

  7. Hey, thanks for the code it works, but I can only hover the pointer over the cell to view the time. It has those # symbols in the cell. Any idea how to fix this so it shows in the cell?

    1. never mind the last post i fixed my idiot problem lol! what I am experiencing is the double click does not work when cells are merged only un-merged cells function properly. Is there some more to add to the VBA?

  8. Hello Deb,
    I track time a lot and I love the double-click to insert a static time. Saves time over the keyboard shortcut. My time tracking needs for a project cover a period of weeks and I must total daily times and also cumulative times per specific job function over the course of the project. Therefore, I need to include the date as well as the time. If I change the cell formatting to what I use, m/d h:mm AM/PM, the date displays as 1/0 1900. How can I modify the code to produce the current date along with the time?
    Cheers,
    Pete

  9. Hello Deb,
    I need a macro code which automatically captures the time and date in one cell and the time should not be edited to make changes. Please help me.

    1. @Aruna, I don’t have any sample code that does what you need. You could protect the worksheet, and lock the Date/Time cell.
      Then, write a little macro to unprotect the sheet, put the value of Now in the date cell, and re-protect the sheet.

  10. Dear Debra, it´s been great following your advice and solutions, and many of those have saved my skin on my personal needs, but I have a question about time as a value in a cell.
    I’m doing a research and my datalog sends me data every 5 minutes, on a spreadsheet. I wonder if it would be possible to use a macro to select all readings every hour and then run an average on those values, copying everything to a new tab, minding that I have the time well specified in a specific cell. Sure you don’t need to do it for me but could you tell me if I am onto something possible or if I have to look in another direction? Twitter(I_will_adapt

    1. Hi Ricardo, if this is something that should happen even if Excel is closed, you could try using the Windows Task Scheduler to open the file.
      Then, have a macro that runs automatically when the file opens, to copy the data, calculate the average, and save and close the file.

  11. Is there a way to use your code above to enter current time but if it is 7 min after the quarter to round down and 8 min past quarter to round up to next quarter

  12. i want cell to freeze with the value once we double click and no more manual editing and no another double click to the cell

  13. Is there way we can track break when system is locked for certain time. For an example if user is away from desk for more than 5 minutes then it should start capturing the idle time and all those events should sum up at day end

  14. Hello,
    Can i allocate a macro like this to a button ?
    Also, I want disable manual update for those columns which time has updated.
    Is it possible?
    Please help me

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.