Auto-Populate Date

  • Thread starter Thread starter robs
  • Start date Start 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
 
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
 
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
 
=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.
 
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
 
Back
Top