Recording the date a value is entered

[Mirlynn]

I need to record the date a value is entered into a cell. That date should not change each time the worksheet is opened - only when the designated field is populated. If the field is cleared the dependent field would be blank as well. Once something were reentered in the designated field, the date at that point should be recorded.

For example. Column M is called "Complete". A "Y" or "N" is recorded in this column for each row to reflect that review of that record (row) has been completed or not. So I want to record the date when Y/N is entered in Column M. Column N should be blank until Col M has a value entered.

Is it possible to do this without using a macro or VBA? My organization frowns on use of Macros due to security concerns. Any solution would be welcome but one not using a macro would be preferred.

This may be an easy question for some. I am not very familiar with VBA but this might be a fun little project to learn more about it. I really appreciate any guidance someone can provide!!! Thanks in advance!!!

 

[VoG]

Sorry but you need a macro. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = Now
    Application.EnableEvents = True
End If
End Sub

 

[njimack]

You'll struggle to achieve this without VBA - if you decide to go the VBA route, the following code, pasted into the module of the sheet in question, should suffice.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 13 Then Cells(Target.Row, 14) = Date
End Sub

 

[Mirlynn]

Thanks for the quick reply! I followed the instructions and I do get a date/time stamp in Col N when Col M has a value entered.

However, if I clear the value in Col M, the Date/Time stamp stays in Col N. Is it possible to clear the D/T stamp if/when the value in Col N is blank?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 Then
    Application.EnableEvents = False
    If Target.Value <> "" Then
        Target.Offset(, 1).Value = Now
    Else
        Target.Offset(, 1).ClearContents
    End If
    Application.EnableEvents = True
End If
End Sub

 

[njimack]

Something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 13 And Target.Value <> "" Then Cells(Target.Row, 14) = Date
End Sub

 

[Mirlynn]

Thanks to both of you for your replies! It appears in both cases, the following bit of VBA is throwing up an error -

If Target.Value <> "" Then

.

The debugger starts when trying to run either version and it points to this line in the VBA.

Is it possible to run excel functions in VBA? Something like "ISBLANK"?

Any suggestions on how to correct the line of VBA above so it will run properly?

 

[njimack]

Thanks to both of you for your replies! It appears in both cases, the following bit of VBA is throwing up an error -

If Target.Value <> "" Then

.

The debugger starts when trying to run either version and it points to this line in the VBA.

Is it possible to run excel functions in VBA? Something like "ISBLANK"?

Any suggestions on how to correct the line of VBA above so it will run properly?

That line of code is the equivalent of ISBLANK.
An alternative would be testing the length of the cell's value...

If Len(Target.Value)>0 Then

 

[Mirlynn]

The last edit worked great!

I wanted say thanks for the help! I really appreciate it and hope that others might also benefit from your support.