Static current date based on when data in another column was enter

[Daniel]

I want to setup a date column that each cell in the date column reflects the
date that another column in the same row was filled in.

Example: I enter a numerical value in cell D5 that is greater than 0. I want
A5 to record the date that I entered that data.

Also I would like to do the same thing with time in cell B5. How could I do
this?

Thanks a lot!

 

[Gary''s Student]

Place the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d5 As Range, t As Range
Set d5 = Range("D5")
Set t = Target
If Intersect(t, d5) Is Nothing Then Exit Sub
If Not IsEmpty(Range("A5")) Then Exit Sub
Application.EnableEvents = False
Range("A5").Value = Date
Range("B5").Value = Time
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Tom Hutchins]

You can do that with a Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("D")) Is Nothing Then
If Len(Target.Value) > 0 And Target.Value > 0 Then
Target.Offset(0, -3).Value = Date
Target.Offset(0, -2).Value = Time
End If
End If
End Sub

This code must be in the code module for the sheet where it should work.
Right-click on that sheet tab and select View Code. The Visual Basic Editor
will be displayed. Paste the above code in the big empty code window. Select
File >> Close to return to regular Excel.

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[Dave Peterson]

Just a suggestion...

Put the date and time in one cell:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myRngToInspect As Range
Dim myIntersect As Range

Set myRngToInspect = Me.Range("D1").EntireColumn

Set myIntersect = Intersect(Target, myRngToInspect)

If myIntersect Is Nothing Then
Exit Sub 'outside of column D
End If

For Each myCell In myIntersect.Cells
Application.EnableEvents = False
With Me.Cells(myCell.Row, "B")
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
Next myCell

End Sub

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the newly opened code window.

Then back to excel to test it.