Timestamp without VBA

[wdgor]

I have a worksheet that needs a timestamp in the cell in the next column to the entry cell - Column N has the cell that requires an entry and Column O has the timestamp (MMM/DD/YYYY H:M:S). That timestamp can't change unless someone updates the cell in Column N, then it gets the new timestamp value. I have developed some VBA code that works perfectly. I am seeking a solution that doesn't need VBA. I know that NOW() and TODAY() won't work "out of the box" because they are dynamic and will update with any change to any cell. Is there a non-VBA solution? Thanks in advance for your insight.

 

[Joe4]

The only way I have seen to do it without manual intervention or VBA is method 3 here:

How to Insert Excel Timestamp When Cell Changes Without VBA (3 Ways)

This article describes 3 easy ways to insert excel timestamp when cell changes without vba. Use these methods to solve your problems easily.

www.exceldemy.com

 

[wdgor]

I will try it (out of the office at the moment) but NOW() will recalculate every time the sheet is reopened. Somehow, it needs to keep the value of when the entry was made and not dynamically update.

 

[Joe4]

I will try it (out of the office at the moment) but NOW() will recalculate every time the sheet is reopened. Somehow, it needs to keep the value of when the entry was made and not dynamically update.

I understand that. Normally that is true. But the key is with the iterative calculation setting and making use of Circular References.
I don't pretend to fully grasp how this method works, but supposedly it does. I don't ever use it myself as I prefer VBA.
But people have used it with success.

 

[wdgor]

I tried it and it works. Thanks for the suggestion.

However, there's always a "however", that works when the cell is empty but, if the user changes the value of the cell, it won't recalculate. I can see why - the formula: =IF(I3<>"",IF(J3<>"",J3,NOW()),"") is looking to see if I3 is <>"". The user will be changing the value in I3 without deleting the first value. In that case, J3 will not change when the new value is entered. Any thoughts on how it could change the timestamp if the value changes?

I do have this working well in VBA but it is posted to SharePoint. The administrators, rightfully so, don't want to enable macros on the site. That is why I am looking for a non-VBA solution., Thanks.

 

[Joe4]

I tried it and it works. Thanks for the suggestion.

However, there's always a "however", that works when the cell is empty but, if the user changes the value of the cell, it won't recalculate. I can see why - the formula: =IF(I3<>"",IF(J3<>"",J3,NOW()),"") is looking to see if I3 is <>"". The user will be changing the value in I3 without deleting the first value. In that case, J3 will not change when the new value is entered. Any thoughts on how it could change the timestamp if the value changes?

I do have this working well in VBA but it is posted to SharePoint. The administrators, rightfully so, don't want to enable macros on the site. That is why I am looking for a non-VBA solution., Thanks.

Note what it says about that:

So, if you want a non-VBA solution, that appears to be a limitation that you cannot get around.
I know of no other non-VBA solution, unless you want to do it all manually.

 

[wdgor]

Thanks, I was afraid of that. I wish Excel had a native function that dealt with "change value". I'll keep playing with it - maybe try a helper cell - and see if I can get around the limitation. Thanks for your thoughts.

 

[Joe4]

You are welcome.