Formula for Creating Static Datestamp/Timestamp in a Cell Relative to a Value is Added in Another Cell.

[Zedrick13]

Hello, everyone. I know you can help me on this. What I am trying to do is create a formula in one cell so that when I put the value "Yes" in another cell within the same row it creates a static date (a datestamp, essentially). Now, I want to make sure that the date doesn't change when I update the spreadsheet or I accidentally change the value "Yes" to something else.

I know I can probably just do any of the following below, but I really need it to be a formula:

1. Ctrl + ;
2. Create a macro with code:

Sub myNow()
activecell.Value = now()
End Sub

3. Enter =NOW() in a cell. After that, to easily change that formula value into a static value:
• Select the cell.
• Press and release the F2 key.
• Press and release the F9 key.
• Hit the Enter key.

So, I would really appreciate it if somebody can help me with the formula.

 

[steve the fish]

You cant do that without VBA.

 

[Zedrick13]

Oh, I already figured it out. I did it using circular reference.

Steps:
1. Enable iterative calculations: File>Options>Formulas>Tick "Enable iterative calculations">Set "Maximum iterations" to 1.
2. Enter formula like below and hit Enter:

=IF(C3="Yes",IF(D3="",NOW(),D3),"")

Note:
1. C3 references the sheet where you enter the value that triggers the date stamp in the adjacent cell (in my case it's the text "Yes") and D3 is the cell where you enter the formula and where the date stamp value appears.
2. D3 should be formatted to date.

Cheers!

 

[Zedrick13]

To steve the fish:
I did actually. Thanks anyway.

 

[steve the fish]

I cant see how that works but if you say its does then ok.