How to prevent each item date from changing after "TODAY" formula

[Marvin]

I wanted to have the date and time automatically appear whenever I entered an
item in my spreadsheet. However, each time I entered a new item, all the
dates and times of the previous items changed as well.

I want to enter items on different days, but I want to track the previous
dates and times of items entered days before. What is happening is that the
next day all the dates and times change. Is there a way to prevent this from
happening?

Marvin

 

[Dave Peterson]

Instead of using the formula =today() (or =now()), you could just put the date
(and time) into that cell:

ctrl-; (control-semicolon)
will enter the date

ctrl-: (control-colon)
will enter the time

ctrl-;(space character)ctrl-:
will enter a date and time

Another alternative would be to use an event macro that looks for a change in a
certain range and then updates another cell with the date/time.

Take a look at JE McGimpsey's site:
http://www.mcgimpsey.com/excel/timestamp.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Luke M]

Not sure how you've got it setup currently, but to input time stamps, you
could try this event macro. Right click on sheet tab, view code, paste this
in. You should probably save your previous time stamps and make them static,
so they don't keep changing.

Private Sub Worksheet_Change(ByVal Target As Range)

'Change letter to match column where you are inputting data
If Target.Address Like "$A$*" Then
'Note: Change 2nd number to number of columns to offset time stamp
Target.Offset(0, 1).Value = Now
End If

End Sub