Auto Date and Time

[bill351]

Hi Guys n Girls I have a data collection sheet on phone calls made to different staff and in that I have column C as current date and column D as time when the call was made and this is automated from a name entered into column A. My current formula is =IF(A1="","",IF($A$1="",NOW(),$A$1))
which does what it is suppose to do however as I progress down my sheet and the time rolls from 13:01 to 13:02 it changes all the previous times above to 13:02. How do I keep the times of the calls to when I called like Jim was called at 13:15 in row 5 and then Carol was called at 13:30 in row 6 and Tony at 14:03 in row 7. In its current form when I put Tony in both Jim and Carol's time changes to 14:03? Thanks in advance Bill from Oz

<colgroup><col><col><col span="2"><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[Joe4]

That will have to be done with VBA. We can set-up a process that will automatically capture the date and time stamp in columns C and D whenever data is manually entered into column A.

So, if you right-click on the sheet tab name at the bottom of the sheet, and enter this code in the resulting VB Editor window, it should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myFirstDataRow As Long
    
'   Specify where the first data row starts
    myFirstDataRow = 2
    
'   Check for updates in column A
    If (Target.Column = 1) And (Target.Row >= myFirstDataRow) And (Target <> "") Then
'       Capture date stamp in column C
        Target.Offset(0, 2) = Date
'       Capture time stamp in column D
        Target.Offset(0, 3) = Time
    End If

End Sub

Note. You may need to change the value of the "myFirstDataRow" variable to reflect which row your data actually starts in.

 

[bill351]

Hi Joe4
Thankyou for that I will give that a go when I return to work Thursday

Cheers
Bill