Locking or freezing the TODAY() formula

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

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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å
    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.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.