static "now" values

[bflorox]

I have a 2-column ss that returns the current date and time in one column
when an entry is made in the neighboring column. I want the dates and times
to be static, meaning, I want those values to stay what they were when the
entry was made instead of all changing to the current date and time whenever
a new entry is made.

 

[Gary''s Student]

A very tiny trick:

First click in the formula bar and then touch:

CNTRL-;
spacebar
CNTRL-SHFT-;
ENTER

This will enter the date followed by a space followed by the time in the cell

 

[bflorox]

This is what I have in column A:

=IF(B11="", "", NOW())

Can those key strokes replace "NOW()" ? I want it so that when the operator
enters a value in column B, a static date and time automatically appear in
column A. Is this possible?

Thanks.

 

[Peo Sjoblom]

http://www.mcgimpsey.com/excel/timestamp.html

--


Regards,


Peo Sjoblom

 

[Gary''s Student]

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set B = Range("B:B")
If Intersect(t, B) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, -1).Value = Now
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

 

[bflorox]

Perfect! Thanks!

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set B = Range("B:B")
If Intersect(t, B) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, -1).Value = Now
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