Date that does not update

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I am trying to use the =NOW() or =Today() formulas to track changes to a
sheet. When A1 is not blank, I need B1 to display the current date and time
(and repeat down B:B). The problem is I can't have the formula update once
the date is there. It would be too much work in this situation to use
ctrl+shift+; or past values.
 
You can use a worksheet_event macro in the sheet code of the active sheet.
When do you want this update to occur.
 
I will be entering a number in cell A1 then pressing enter to go to A2 to
enter another number. I would like B1 to show the date and time when I press
enter and then never update that date or time unless I change cell A1 again.
I will be entering data in A:A, so B:B will contain the date and time.
 
Right click sheet tab>view code>insert this. Voila for cell a1. If you want
for entire columns, see version 2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or _
Target.Address <> Range("a1").Address Then Exit Sub
Range("b1") = Date
End Sub
'=========for entire columns
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or Target.column<>1 Then Exit Sub
target.offset(,1) = Date
End Sub
 
That worked great. Thanks! Is there any way to put in the time in B beside
the date or in C?
 
just change =date to
=now
and, IF NECESSARY, preformat your column b as

m/d/yyyy h:mm
 
Back
Top