Documenting Date a Specific Selection Field Option is Selected

We have a project status field, which includes many status types, one of which is “Complete”. I want to have a hidden field document the date the Selection field is changed to “Complete”, so this date can be used elsewhere and for calculations. I tried using IF(D18.RAW=‘Complete’,TODAYTZ(),""), which does insert the date someone changes the field to “Complete” but when someone alters the page later, like for billing, the date changes to the new day.

Anyone know of a way to document the date this field is changed as desired?


TODAYTZ() in formulas would be updated once the entry is recalculated. If you would like to record the date that D18 is modified to Complete, you could try update value in this sheet button to update D18 and date field with the variable {{TODAY}} or {{NOW}}, the date field updated won’t be changed by recalculation.

Thanks. This sounds helpful and I’ll give it some thought. However, I’m trying to do this behind the scenes with no extra actions by the user.