Date stamp iterative method failing

[lwhyatt]

I'm using the formula:


=IF(E3<>"",IF(K3<>"",K3,NOW()),"")

to date stamp in K3 when an entry in E3 is made. I have itterative calculation enabled to 1000 iterations. I have formated K column to date.

However, whatever I input the date 00/01/1900 is returned.

What am I doing wrong to get this method to work?

 

[lwhyatt]

I should add, if I delete the figure currently in E3, then add it back in again it works. I want it to do nothing to the contents of K3 until a new figure is typed into E3, overwriting what is currently there.

 

[w0kkie]

Hi @lwhyatt, I realize this is an old post now but came across it in a search and noticed no answer was shared, so thought I would share what I learned by looking for something similar, which may be what you're looking for. Give it a try (place the code in cell K3, based on your parameters above):

=IF(E3<>"",IF(AND(K3<>"",CELL("address")=ADDRESS(ROW(E3),COLUMN(E3))),NOW(),IF(CELL("address")<>ADDRESS(ROW(E3),COLUMN(E3)),K3,NOW())),"")

* Also, there should be no need to set iterations to 1000, unless you need that for a different purpose. Even using 1 iteration would work for this kind of calculation where it is just a logical operation and not a numerical/mathematical one, and might avoid you having a long wait if you accidentally include a circular reference in a complex formula somewhere down the road.