Auto enter date when data in enter in another cell

[Brian]

Howdy All,

I want the date enter in column A when data in enter in column B.
I want this date to remain constant, meaning that once it is enter as
12/4/2006, that it will not change.

Thanks,
Brian

 

[Ron de Bruin]

Hi Brian

You can use the change event for this in the sheet module

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

 

[Brian]

No way to do this without VBA?

If you are not adverse to VBA macros this is fairly easy to do. Otherwise
you
are going to have to rely on the person entering the data to add the date
manually. it is quick and easy to add the current date using
Ctrl + ;
If you want the code solution here it is
Right Click the Tab where you want the dates added and select View Code.
Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then Target.Offset(0, -1).Value = Now()
End Sub

This code adds both date and time. If you only want to see the date just
reformat the column to only show the date.

 

[Gord Dibben]

Brian

No "auto" without VBA

Manually hit CTRL + ;(semi-colon) to enter a static date.


Gord Dibben MS Excel MVP