Logging a time

[SandsB]

=IF(B2<>"",NOW(),"")
This fills in a date/time if B2 is not blank. But any change in the sheet updates the value of NOW(). What I'm looking for is a way to make a log sheet where this date/time doesn't change once the cell in column B is populated. So a user enters their ID in column B and column C is populated with the current time - and that time doesn't change.

 

[Joe4]

This is one of the most common asked and answered questions on this forum (there are literally thousands of posts on it!)
This can be done with a simple VBA code.

Right-click on the sheet tab name at the bottom of your screen, select "View Code" and paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Row > 1 Then
        Target.Offset(0, 1) = Now()
    End If
End Sub

Now, as you make manual entries in column B, column C will automatically be updated with the Time Stamp.

 

[Herakles]

=IF(B2<>"",NOW(),"")
This fills in a date/time if B2 is not blank. But any change in the sheet updates the value of NOW(). What I'm looking for is a way to make a log sheet where this date/time doesn't change once the cell in column B is populated. So a user enters their ID in column B and column C is populated with the current time - and that time doesn't change.

This code if you only want the time to be added when the cell in column B is first populated and not if it is subsequently updated.

Change the following line depending on what time format you need.

.NumberFormat = "hh:mm:ss"

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then
        Exit Sub
    End If

    If Not Intersect(Range("B:B"), Target) Is Nothing And Len(Trim(Target.Offset(0, 1))) = 0 Then
        With Target.Offset(0, 1)
            .Value = Now()
            .NumberFormat = "hh:mm:ss"
        End With
    End If

End Sub

 

[SandsB]

Ahhhhh. Thank you. I did look but my search terms must suck as much as my knowledge of Excel.

 

[Herakles]

Ahhhhh. Thank you. I did look but my search terms must suck as much as my knowledge of Excel.

This goes in the Worksheet code module just in case you don't know.

 

[Joe4]

This goes in the Worksheet code module just in case you don't know.

Yep, that part often confuses people, which is why I always provide this instruction to ensure not only that they put it in a Worksheet module, but that they also put it in the correct Worksheet module (in case they have multiple sheets):

Right-click on the sheet tab name at the bottom of your screen, select "View Code" and paste this code in the VB Editor window that pops up:

If they always navigate to it that way, it will ensure that they are in the right place!