Variation on: Timestamp of a dynamic cell

[raggd]

hi all,

I've been working on a document that needs to timestamp a dynamic cell, and so far looking through forums I have found the useful below formula along with circular references:

=IF($V8=$X$3,IF(X8="",NOW(),X8),"")

This gives a timestamp if it equals $X$3 but blank otherwise.

My problem is that i would like three timestamps. The dynamic cell has options for a project of "Open", "Closed", and "Behind Schedule". I think I need a formula that is ONCE IF is reached, THEN never recalculate.
Just now every time the cell is changed, a new timestamp is given, but the old timestamp is erased. How can I keep all of them?

 

[Chihiro]

This post should help if you haven't looked at it already.
http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/

Otherwise, if you are not averse to VBA I find it easier.

 

[raggd]

Hi Chihiro,

yeah I'd looked at that one previously, and that one has the same problem as the variation on the formula I used above; namely when the dynamic cell is changed (see the attachment), it overwrites the original timestamp or leaves it blank.
As for VBA I've been asked to keep it as simple as possible or nonexistent i won't be the owner of this so we want to keep it easy if possible

 

[Chihiro]

Hi raggd,

Only other option I can think of beside VBA... is to have 4 separate tracking column for each Status and do iterative calculation off of each column.

However, it will still recalculate. If existing value for status columns are changed.

 

[Chihiro]

Just thought of something. This may work.
Change formula to:

=IF($A2=$B$1,IF(B2=B2,NOW(),B2),B2)

And add blank value to your named range.

Unfortunate side effect is that the cell will not default to empty and will have 12:00:00 AM if it to start with. But will retain time stamp even if Status is changed second time.

 

[raggd]

I think that might be a nearly perfect answer, thank you so much Chihiro! I can definitely use this.