My pre-populated date keeps changing to the day I open the file

Brass Contributor

Greetings everyone.


I got an excel sheet that populates the date when someone selects a drop down of names.   This means that person completed a task. 


The problem is, when I open the file on another day, the dates change to today.

I just need that date to be the original date when it was originally selected and not change to today unless the name changes.  


Thank you in advance for your assistance.


=IF(OR(J5<>"", K5<>""), TODAY(), "")





3 Replies


I've added this code to your file which returned the expected result in my sample.

Sub Worksheet_Change(ByVal Target As Range)

Dim rngBereich As Range

Set rngBereich = Range("J4:K100")

If Target.Cells.Count > 1 Then GoTo done

If Not Application.Intersect(Target, rngBereich) Is Nothing Then

If Target.Column = 10 And Target.Value <> "" Then
Target.Offset(0, 2).Value = Date
If Target.Column = 11 And Target.Value <> "" Then
Target.Offset(0, 1).Value = Date
End If
End If

End If

Application.EnableEvents = True
Exit Sub

End Sub


I appreciate the response.

Just out of curiosity, is there a way to avoid VBA? I ask only because this will be a shared file, in the past had a bad experience with not everyone user getting the VBA to work without an update.


You are welcome. As far as i know we have to use VBA for this.