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.

 

[Yogi Anand]

Thanks Ivan for this helpful information. I have not located the item on the old board yet, but I have gone ahead and played with it and it does indeed work.
I used to think that the only way I could convert some formulas into values was to either Copy and Paste them as Values, or use VBA to do it. This technique reads the value of the formula and paste it in the Data_Validated cell. I am sure it has some intriguing implications for me. Again thanks to Brian from Maui for mentioning it and to you for making me go ahead and explore it.
This message was edited by Yogi Anand on 2002-05-20 19:51

 

[Brian from Maui]

Aloha Yogi and Ivan

The credit goes to Andy Gee on the old board.

 

[Brian]

You CAN do it with formulas!

Jump to the bottom for the 1 cell formula, or if you want the story of discovery:

A1 = IF(ISBLANK(C1),"",B1)
B1 = IF(A1="",NOW(),A1)
C1 = The cell to be evaluated

Now go to Tools>Options>Calculation and turn on iteration (worked with the default 100 / 0.001 an 1 / 0.001)

With C1 blank:
A1 is blank
B1 will change to the current time at each calculation.

When a value is entered in C1:
A1 takes the value of B1 and then never changes. (Not even with workbook save/close/open)

CAVEAT: If the worksheet is in automatic mode, then B1 will change to the date/time when opened or closed. However, if you don't do anything that causes a recalculation before you enter a value in C1, then you will get the date/time of the LAST CALCULATION, not the CURRENT time!

This is solved by switching the cells. I presume that Excel generally calculates in some order cell(1,1) then (1,2) ... (2,1) but I don't know how to test that.

A1 = IF(B1="",NOW(),B1)
B1 = IF(ISBLANK(C1),"",A1)
C1 = The cell to be evaluated

Now it shows the Date/time that C1 was entered!!!

I tried combining it into one cell:
=IF(A1="",NOW(),IF(ISBLANK(C1),"",A1))
But then it only works when C1 is first entered. If C1 is cleared, then the date stays. Also, need to set at least 2 iterations to get the current time when C1 is entered.

GOT IT IN ONE FORMULA!
A1 =IF(AND(A1="",NOT(ISBLANK(C1))),NOW(),IF(ISBLANK(C1),"",A1))

{works with 1 or more iterations)

Thanks for the challenge!

I have always used macros when I needed this.

 

[Brian]

To record the date/time data was first entered anywhere in the row:

=IF(AND(A1="",NOT(COUNTA(B1:IV1)=0)),NOW(),IF(COUNTA(B1:IV1)=0,"",A1))

But, what I usally want is the latest date something was added or changed. I think I need a macro for that. (or other columns to somehow count the previous count of non-blank cells.

 

[kasprick]

To record the date/time data was first entered anywhere in the row:

=IF(AND(A1="",NOT(COUNTA(B1:IV1)=0)),NOW(),IF(COUNTA(B1:IV1)=0,"",A1))

But, what I usally want is the latest date something was added or changed. I think I need a macro for that. (or other columns to somehow count the previous count of non-blank cells.

Using a similar formula, can I create a static date when I enter data into a specific cell? When I enter data into cell A1, can I automatically create a static date in B1. I would prefer not to use VBA if I can help it.

 

[libwolf]

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

[SIZE=-1][ This Message was edited by: Jay Petrulis on 2002-05-17 08:40 ][/SIZE]

How do I reverse this? So that when I add data to B2, A2 adds the time the data was entered? I've tried swapped around Target.Offset but that didn't work too well in that it populated A2 to ∞ with the time.

Thanks,
Libwolf

 

[LearnMoreReadMoreDoMore]

I Just Tried JAYS Code and it WORKS !!

I am so grateful that I found this message board!

Here is my working copy along with a picture to remind yourself to ENABLE Macro each time you open the file... otherwise it wont work. (saved it as a Macro Enabled File =D )

https://www.dropbox.com/s/v4f0xzrisnp9sfn/TimeStampTracker.zip?dl=0


https://www.dropbox.com/s/v4f0xzrisnp9sfn/TimeStampTracker.zip?dl=0

 

[Umangka]

Hello Brian,

I used the above A, B and C method but it always comes with an error message that there are one or more circular references where a formula refers to its own cell value ! can you help me out with it?
Thank you!

 

[InkieSquid]

I acknowledge that this is an exceptionally old post, however I would still like to thank easpeed for posting this question as well as the very valuable answers provided by respondents, especially Jay Petrulis.
My needs are to make a diary so that I can log medication that I take occasionally, but I am terrible at keeping diaries, so having the time and date populate just by me entering a string with the meds I've just taken is exceptionally helpful.
Answer 1 worked instantly for me (though I needed to change the workbook save file to one that allows macros) and while I was tempted to tinker further it wasn't needed for me this time as I can now just jot down some drugs, auto-populate the date and time, save and go