Automatically entering date

K

keith

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

David F Cox

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

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

Scott Marcus

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
 
D

Don Guillett

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
 
D

Don Guillett

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

Scott Marcus

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 said:
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
Click to expand...

Thanks again,
Scott
 
D

Don Guillett

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
[email protected]
Scott Marcus said:
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 said:
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
Click to expand...

Thanks again,
Scott
Click to expand...
 
Top