Auto-Populate Date

R

robs

I need some help.

I want to write a formula that will auto-populate cell A2 (for example)
with the date when cell A3 (for example) has data entered in to it.
Also, I would like cell A2 to remain blank if no data is in cell A3.
Could someone please let me know how this can be done?

Thank you,
Rob
 
R

Ron de Bruin

Hi Robs

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("a3"), Target) Is Nothing Then
If Target <> "" Then
Target.Offset(-1, 0).Value = Format(Now, "mm-dd-yy hh:mm:ss")
Else
Target.Offset(-1, 0) = ""
End If
End If
End Sub

You can do it with the change event of the worksheet
This example will place the date/time in the A2 if you change
A3

Place the code in the Sheet module

Right click on a sheet tab and choose view code
Paste the code there
Alt-Q to go back to Excel
 
R

robs

Ron de Bruin,

Thank you very much for this information, I tried it out and it work
great. One last question for you. What if I wanted to do this b
column or groups of cells. So that it would populate a single cel
(ex. A2) in column A with the date when a single cell in column B (ex
B2) has data entered in to it.

Thank you,
Ro
 
A

Adam

=if(a3="","",today()

Although remember that the today() function will re-calculate when the open the spreadsheet on another day therefore losing the day entered so remember to copy cell A2 containing this formula and paste as value.
 
R

Ron de Bruin

Hi Rob

Try this for the range B1:B1000

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("b1:b1000"), Target) Is Nothing Then
If Target <> "" Then
Target.Offset(0, -1).Value = Format(Now, "mm-dd-yy hh:mm:ss")
Else
Target.Offset(0, -1) = ""
End If
End If
End Sub
 
Top