Locking or freezing the TODAY() formula

David Budnick
David Budnick ✭✭
edited 12/09/19 in Formulas and Functions

Good day all, I've looked around in the community about locking or freezing a current date, but I'm still having issues. My goal is to lock a start date that's triggered by entering a X and have that current date freeze until the x is removed. 

When an X is entered in the Visit column, that triggers a start date to populate which triggers a next visit date to populate. I've been successful in excel, but not smartsheet (yet). The formula in excel is a iterative calculation where N8 stands for Visit and M8 stands for Start Date. The formula I'm using in excel is =IF(N8 = "","",IF(M8="",TODAY(),M8)) and this freezes the start date accordingly, but not so much in Smartsheet.  

My smartsheet formulas right now are; Start Date =IF(Visit7 = "X", TODAY()) This then populates the Next Visit date which is formula =IF([Meeting Frequency]7 = "ANNUAL", [Start Date]7 + 365)

My current sheet below  shows the 2/22/19 date which will change tomorrow and needs to be "locked" once the X is entered and remain 2/22/19  until the X in Visit is removed

Could anyone help me correct the Start Date formula to what it needs to be? 

Thank you

 

Smartsheet screen shot.JPG

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!