Non-volatile timestamp (revisited)

[easpeed]

I have a problem similar to Ozone64's posting of 4/23 that I am trying to generate automatic time/datestamps in a cell whenever a value is entered in an adjacent cell. I am currently using the function =IF(A3<>"",NOW()) but the problem is when data is entered in subsequent rows the previous timestamp is overridden with the new timestamp. Thus at the conclusion of data entry every row ends up with the same date/timestamp as the final entry.

Any help is appreciated.

 

[Jay Petrulis]

Hi,

You can use an event macro to do this.

Right click on the sheet tab and choose View Code. Then, copy the following and exit the VB editor to go back to Excel

'-----------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count = 1 And Target.Column = 1 Then
Target.Offset(0, 1) = Now
End If
End Sub
'------------------

This will add the date/time stamp in column B at any change in column A.

HTH,
Jay
This message was edited by Jay Petrulis on 2002-05-17 08:40

 

[Brian from Maui]

easpeed,

I hope someone can automate this concept using data validation, without a macro (only because I don't know enough about macro's and how to use them). I got this from the archives.
In a cell enter =now() or =today()
In the cell where you want the date, go to data validation, select list and reference the cell with now or today. Then the date or time does not change.

 

[Yogi Anand]

On 2002-05-17 08:48, Brian from Maui wrote:
easpeed,

I hope someone can automate this concept using data validation, without a macro (only because I don't know enough about macro's and how to use them). I got this from the archives.
In a cell enter =now() or =today()
In the cell where you want the date, go to data validation, select list and reference the cell with now or today. Then the date or time does not change.

Hi Brian:

=TODAY(), and =NOW() will keep updating the dates dynamically ... to turn them into static values, these will have to copied as values at each instance of date stamping.

So in my opinion, an event oriented VBA based solution is the way to go on this one.

Regards!
This message was edited by Yogi Anand on 2002-05-17 08:53

 

[Brian from Maui]

Yogi,

I understand. My point being, maybe someone like yourself, Mark W., Aladin, or Jay could take this concept one step further a make it not as cumbersome or automate someway.

 

[easpeed]

On 2002-05-17 08:52, Yogi Anand wrote:

On 2002-05-17 08:48, Brian from Maui wrote:
easpeed,

I hope someone can automate this concept using data validation, without a macro (only because I don't know enough about macro's and how to use them). I got this from the archives.
In a cell enter =now() or =today()
In the cell where you want the date, go to data validation, select list and reference the cell with now or today. Then the date or time does not change.

Hi Brian:

=TODAY(), and =NOW() will keep updating the dates dynamically ... to turn them into static values, these will have to copied as values at each instance of date stamping.

So in my opinion, an event oriented VBA based solution is the way to go on this one.

Regards!
This message was edited by Yogi Anand on 2002-05-17 08:53

I agree Yogi, my issue is I have two other columns I have automatically populating based upon the entry in column A. So data is entered in column A and then formulas in column B & C populate those adjacent fields and I want the date/timestamp to populate in column D.

Any idea how I might tweak the VB to get this to work?

 

[easpeed]

On 2002-05-17 08:52, Yogi Anand wrote:

On 2002-05-17 08:48, Brian from Maui wrote:
easpeed,

I hope someone can automate this concept using data validation, without a macro (only because I don't know enough about macro's and how to use them). I got this from the archives.
In a cell enter =now() or =today()
In the cell where you want the date, go to data validation, select list and reference the cell with now or today. Then the date or time does not change.

Hi Brian:

=TODAY(), and =NOW() will keep updating the dates dynamically ... to turn them into static values, these will have to copied as values at each instance of date stamping.

So in my opinion, an event oriented VBA based solution is the way to go on this one.

Regards!
This message was edited by Yogi Anand on 2002-05-17 08:53

I agree Yogi, my issue is I have two other columns I have automatically populating based upon the entry in column A. So data is entered in column A and then formulas in column B & C populate those adjacent fields and I want the date/timestamp to populate in column D.

Any idea how I might tweak the VB to get this to work?

 

[Jay Petrulis]

Hi,

To get the time/date stamp in column D, change

target.offset(0,1)

to

target.offset(0,3) = Now
or
Range("D" & Target.Row) = Now

I don't think a Data Validation option is possible, because that will only bound the allowable entries, not enter something inthe cell.

Bye,
Jay

 

[easpeed]

Thank you so much Jay. The first option did the trick.

On 2002-05-17 09:27, Jay Petrulis wrote:
Hi,

To get the time/date stamp in column D, change

target.offset(0,1)

to

target.offset(0,3) = Now
or
Range("D" & Target.Row) = Now

I don't think a Data Validation option is possible, because that will only bound the allowable entries, not enter something inthe cell.

Bye,
Jay

 

[Ivan F Moala]

On 2002-05-17 08:52, Yogi Anand wrote:

On 2002-05-17 08:48, Brian from Maui wrote:
easpeed,

I hope someone can automate this concept using data validation, without a macro (only because I don't know enough about macro's and how to use them). I got this from the archives.
In a cell enter =now() or =today()
In the cell where you want the date, go to data validation, select list and reference the cell with now or today. Then the date or time does not change.

Hi Brian:

=TODAY(), and =NOW() will keep updating the dates dynamically ... to turn them into static values, these will have to copied as values at each instance of date stamping.

So in my opinion, an event oriented VBA based solution is the way to go on this one.

Regards!
This message was edited by Yogi Anand on 2002-05-17 08:53

Hi Yogi, The technique that Brian descibes
works, it is something that was posted @ the
old board. Try it out, you will see that
data validation will hold its initial value
when set even when you recalculate, close & reopen etc. Try it ! :wink: