Locking or freezing the TODAY() formula

David Budnick

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

 

Andrée Starå

Hi David,

Unfortunately, it's not possible at the moment, but it's a great idea!

Please submit an Enhancement Request when you have a moment.

It's possible to set it up with the help of a third-party tool like Zapier or similar.

Would that work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Djarano Faerber

@Andrée Starå do you know if this option is still not possible?

Andrée Starå

Hi @Djarano Faerber,

What do you want to do more specifically?

Can you describe your process in more detail?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Djarano Faerber

Hi @Andrée Starå,

It is about David Budnick his post.

"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 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."

Your answer was that this wasn't possible yet, I was wondering if still function is already implemented.

Andrée Starå

@Djarano Faerber,

Unfortunately, it's still not possible in Smartsheet. The best option to use would be the third-party service, Zapier.