Excel Time Formula
 

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??

Excel Time Formula
 

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


"M.ROD" wrote:

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??

Excel Time Formula
 

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" wrote:

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??

Excel Time Formula
 

It works..Thanks!

"Mike H" wrote:

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" wrote:

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??

Excel Time Formula
 

thanks alot!

"Gary''s Student" wrote:

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


"M.ROD" wrote:

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??