Formatting a static time in a single cell for excel

[pmm6]

I want to set up a format that will record the time (hh:mm) at the time the
data was entered in and disable the function in that cell after it's used.
Data clerk enters data in A1, and then a function automatically enters the
DATE in A2 and the TIME in A3. The problem is that Excel updates the sheet
after each entry. So, the TIME updates to the current time of the last entry.
Right now I have to enter the time in manually a 100 time a day. I'm using
the "NOW()" for the current date and current time. Any and all help will be
gratefully appreciated.

 

[Frank Kabel]

Hi
see:
http://www.mcgimpsey.com/excel/timestamp.html

 

[Gord Dibben]

Worksheet Event code can do that.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Format(Now, "hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Alternative if you want to be able to edit column A without B
changing.........

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Col B time will not change if data in Col A is edited
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Format(Now, "hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Copy/paste one of the above into a worksheet module. Right-click on a sheet
tab and "View Code" to open a module.

Gord Dibben Excel MVP