Excel Time Formula

[M.ROD]

Enter "s" to record
B C D
call # timestamp timestamp
1 s 1:34:29 PM
2 s 1:34:29 PM
3 N/A
4 N/A
FORMULA: =IF(C5="s",NOW(),"N/A")
I would like capture the current time in cell D when I enter "s" in a cell
C. Problem is when I enter "s" into another cell excel updates all the "s"
cells to the current time. I do not want it to update all the cells to
current time, just record the current time at the time I enter "s"...Is that
possible??

 

[Gary''s Student]

First fill column D with "N/A" and then enter this event macro in the
worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("C:C")
Set t = Target
If Intersect(r, t) Is Nothing Then Exit Sub
If t.Value = "s" Then
Application.EnableEvents = False
t.Offset(0, 1).Value = Now
t.Offset(0, 1).NumberFormat = "[$-F400]h:mm:ss AM/PM"
Application.EnableEvents = True
End If
End Sub

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("C1:C1000")) Is Nothing Then
If UCase(Target.Value) = "S" Then
Application.EnableEvents = False
Target.Offset(, 1) = Time
Application.EnableEvents = True
End If
End If
End Sub

Mike

 

[M.ROD]

It works..Thanks!

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("C1:C1000")) Is Nothing Then
If UCase(Target.Value) = "S" Then
Application.EnableEvents = False
Target.Offset(, 1) = Time
Application.EnableEvents = True
End If
End If
End Sub

Mike

 

[M.ROD]

thanks alot!

First fill column D with "N/A" and then enter this event macro in the
worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("C:C")
Set t = Target
If Intersect(r, t) Is Nothing Then Exit Sub
If t.Value = "s" Then
Application.EnableEvents = False
t.Offset(0, 1).Value = Now
t.Offset(0, 1).NumberFormat = "[$-F400]h:mm:ss AM/PM"
Application.EnableEvents = True
End If
End Sub

--
Gary''s Student - gsnu200786

Enter "s" to record
B C D
call # timestamp timestamp
1 s 1:34:29 PM
2 s 1:34:29 PM
3 N/A
4 N/A
FORMULA: =IF(C5="s",NOW(),"N/A")
I would like capture the current time in cell D when I enter "s" in a cell
C. Problem is when I enter "s" into another cell excel updates all the "s"
cells to the current time. I do not want it to update all the cells to
current time, just record the current time at the time I enter "s"...Is that
possible??