Recording the date a value is entered

Mirlynn

New Member
Joined
Dec 5, 2010
Messages
11
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!!!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Sorry but you need a macro. Right click the sheet tab, select View Code and paste in

Code:
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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.

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

Mirlynn

New Member
Joined
Dec 5, 2010
Messages
11
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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Try

Code:
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Something like this...

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

Mirlynn

New Member
Joined
Dec 5, 2010
Messages
11

ADVERTISEMENT

Thanks to both of you for your replies! It appears in both cases, the following bit of VBA is throwing up an error -
Code:
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Thanks to both of you for your replies! It appears in both cases, the following bit of VBA is throwing up an error -
Code:
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...

Code:
If Len(Target.Value)>0 Then
 

Mirlynn

New Member
Joined
Dec 5, 2010
Messages
11
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.
 

Forum statistics

Threads
1,144,364
Messages
5,723,937
Members
422,527
Latest member
TotalBeginner201

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top