Fix Date to stop it from updating

[rlnicoli]

I am trying to have a formula enter the current date when another cel
is filled in. If you use Today() or Now() the date cell always update
to the current date. I want it to enter the current date and then sta
that date in the future. How do you fix or break the link?
Thank

 

[Jason Morin]

NOW() and TODAY() are volatile functions and will update
every time the worksheet or workbook are recalculated.

You need to use a Worksheet_Change event that stamps the
time in when the cell is changed. Right-click on the tab,
View Code, and insert this:

Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
With Target
If .Count > 1 Then Exit Sub
If .Address = "$A$1" Then
Application.EnableEvents = False
If Range("B1").Value = "" Then
Range("B1").Value = Format(Now, "mm/dd/yy hh:mm:ss")
End If
End If
End With
ErrHandler:
Application.EnableEvents = True
End Sub

 

[rlnicoli]

Jason, This is REALLY CLOSE to what I am looking for. The only tweak i
that I need all of Column B to recognize when the same row Column A cel
changes, i.e. A1 to B1, A2 to B2, A3 to B3, etc.
Can your code be tweaked to allow this.
Than

 

[Jason Morin]

Sure.

Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
With Target
If .Column = 1 Then
Application.EnableEvents = False
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Value = _
Format(Now, "mm/dd/yy hh:mm:ss")
End If
End If
End With
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Gord Dibben]

Manually enter a fixed date by hitting CRTL + ;(semi-colon)

Automatically entering a fixed date when a cell is filled in requires
worksheet event code.

See John McGimpsey's site for code.

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

Gord Dibben Excel MVP

 

[rlnicoli]

THANK