i am using the below formula to update a date in a cell when another cell is
populated.

=IF(B2<"",TODAY(),"")

However, when opened the next day the date updates to the current day
despite the fact that cell B2 is not modified. Is there a way or formula to
ensure that the date remains the same as when it was originally entered?

Not if you want to keep it a formula.

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

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

keith wrote:

i am using the below formula to update a date in a cell when another cell is
populated.

=IF(B2<"",TODAY(),"")

However, when opened the next day the date updates to the current day
despite the fact that cell B2 is not modified. Is there a way or formula to
ensure that the date remains the same as when it was originally entered?

--

Dave Peterson

If I wanted the date to populate only when a certain value is entered into
the cell, how would I modify J.E. McGimpsey's macro?

Thank,
Scott

"Dave Peterson" wrote:

Not if you want to keep it a formula.

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

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

keith wrote:

i am using the below formula to update a date in a cell when another cell is
populated.

=IF(B2<"",TODAY(),"")

However, when opened the next day the date updates to the current day
despite the fact that cell B2 is not modified. Is there a way or formula to
ensure that the date remains the same as when it was originally entered?

--

Dave Peterson

Without looking, put this is the sheet module. This will put the date in
cell f1 if a value of more than 22 is entered in cell f1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$F$1" Then Exit Sub
Application.EnableEvents = False
If Target 22 Then
Target = Date
Else
Target = ""
End If
Application.EnableEvents = True
End Sub
--
Don Guillett
SalesAid Software

"Scott Marcus" wrote in message
...
If I wanted the date to populate only when a certain value is entered into
the cell, how would I modify J.E. McGimpsey's macro?

Thank,
Scott

"Dave Peterson" wrote:

Not if you want to keep it a formula.

J.E. McGimpsey shows a way to put a time stamp on the same row when
something
changes:

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

keith wrote:

i am using the below formula to update a date in a cell when another
cell is
populated.

=IF(B2<"",TODAY(),"")

However, when opened the next day the date updates to the current day
despite the fact that cell B2 is not modified. Is there a way or
formula to
ensure that the date remains the same as when it was originally
entered?

--

Dave Peterson

Well, what I wanted to do is if the cell in column F reads "Closed" than the
date that the word Closed entered cell F would show up in cell J of the same
row. J.E. McGimpsey's macro puts the date in when any value is entered, but
again, I only want it when the cell says "Closed". I figured there's just
one line I could replace with another to fix it, but I can't seem to figured
out what it is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub


"Don Guillett" wrote:

Without looking, put this is the sheet module. This will put the date in
cell f1 if a value of more than 22 is entered in cell f1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$F$1" Then Exit Sub
Application.EnableEvents = False
If Target 22 Then
Target = Date
Else
Target = ""
End If
Application.EnableEvents = True
End Sub

Thanks again,
Scott
--
Don Guillett
SalesAid Software

"Scott Marcus" wrote in message
...
If I wanted the date to populate only when a certain value is entered into
the cell, how would I modify J.E. McGimpsey's macro?

Thank,
Scott

"Dave Peterson" wrote:

Not if you want to keep it a formula.

J.E. McGimpsey shows a way to put a time stamp on the same row when
something
changes:

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

keith wrote:

i am using the below formula to update a date in a cell when another
cell is
populated.

=IF(B2<"",TODAY(),"")

However, when opened the next day the date updates to the current day
despite the fact that cell B2 is not modified. Is there a way or
formula to
ensure that the date remains the same as when it was originally
entered?

--

Dave Peterson

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("f2:f1000"), Cells) Is Nothing Then
'Application.EnableEvents = False 'not needed
If UCase(Target) = "CLOSED" Then Target.Offset(, 4) = Date
End If
'Application.EnableEvents = True 'not needed
End Sub

--
Don Guillett
SalesAid Software

"Scott Marcus" wrote in message
...
Well, what I wanted to do is if the cell in column F reads "Closed" than
the
date that the word Closed entered cell F would show up in cell J of the
same
row. J.E. McGimpsey's macro puts the date in when any value is entered,
but
again, I only want it when the cell says "Closed". I figured there's just
one line I could replace with another to fix it, but I can't seem to
figured
out what it is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub


"Don Guillett" wrote:

Without looking, put this is the sheet module. This will put the date in
cell f1 if a value of more than 22 is entered in cell f1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$F$1" Then Exit Sub
Application.EnableEvents = False
If Target 22 Then
Target = Date
Else
Target = ""
End If
Application.EnableEvents = True
End Sub

Thanks again,
Scott
--
Don Guillett
SalesAid Software

"Scott Marcus" wrote in message
...
If I wanted the date to populate only when a certain value is entered
into
the cell, how would I modify J.E. McGimpsey's macro?

Thank,
Scott

"Dave Peterson" wrote:

Not if you want to keep it a formula.

J.E. McGimpsey shows a way to put a time stamp on the same row when
something
changes:

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

keith wrote:

i am using the below formula to update a date in a cell when another
cell is
populated.

=IF(B2<"",TODAY(),"")

However, when opened the next day the date updates to the current
day
despite the fact that cell B2 is not modified. Is there a way or
formula to
ensure that the date remains the same as when it was originally
entered?

--

Dave Peterson

Private Sub Worksheet_Calculate()
'=IF(B2<"",TODAY(),"")
If Range("b2") = 22 Then Range("b3") = Date
End Sub

--
Don Guillett
SalesAid Software

"Scott Marcus" wrote in message
...
If I wanted the date to populate only when a certain value is entered into
the cell, how would I modify J.E. McGimpsey's macro?

Thank,
Scott

"Dave Peterson" wrote:

Not if you want to keep it a formula.

J.E. McGimpsey shows a way to put a time stamp on the same row when
something
changes:

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

keith wrote:

i am using the below formula to update a date in a cell when another
cell is
populated.

=IF(B2<"",TODAY(),"")

However, when opened the next day the date updates to the current day
despite the fact that cell B2 is not modified. Is there a way or
formula to
ensure that the date remains the same as when it was originally
entered?

--

Dave Peterson

If you are prepared to enable circular references then, assuming the date
is in B6 :

=IF(AND(B2<"", B6=""),TODAY(),"")


"keith" wrote in message
...
i am using the below formula to update a date in a cell when another cell
is
populated.

=IF(B2<"",TODAY(),"")

However, when opened the next day the date updates to the current day
despite the fact that cell B2 is not modified. Is there a way or formula
to
ensure that the date remains the same as when it was originally entered?