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.

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

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

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

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.

@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

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 ;-)

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?

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?

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

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.