Creating a non-updating date

kkunzler

This problem specifically references two columns - The first being a flag column and the second being a date column.  When the flag column is flagged, I would like the date column to get the date the flag column was flagged - and stay that date.  

Here is the formula I am using right now:

=IF([Flag Is Flagged]4 = 1, TODAY())

This isn't working because every day the flag column is flagged for that row, it updates the "Flagged Date" to be today.  I need the Flagged Date to *stay the date the flag was tuned on*, whether it was today, last week, or last month.  

Is there a way to get the current date ONCE, put it into a date field, and have it not update as the days pass? 

I've considered pulling the DAY(), MONTH(), and YEAR() out of TODAY, but the problem remains that as the date in today changes, so does the value in my "Date Flagged"  date column.  

J. Craig Williams

It can't be done using the existing Smartsheet GUI functionality.

There are many posts with comments on why elsewhere.

For small numbers of sheets, I use this method:

http://ronin-global.com/2016/12/11/smartsheet-fixed-date-upon-completion/

For larger numbers of sheets, I use the API.

Craig

kkunzler

Thank you for your response! 

It is frustrating that it just simply *Isn't Possible* but I suppose it makes me feel a bit better that I wan't missing something obvious.  

The "Zap" mentioned required a paid Zap membership, something that my company can't justify for this one set of column (though I'm sure it could be useful elsewhere) 

Anyhow, thanks again for your response and informative link

J. Craig Williams

kkunzler,

I understand the frustration on cost.

I wrote this post a while ago discussing the ROI of automation:

http://ronin-global.com/2016/05/08/to-automate-or-not-to-automate-that-is-the-question/

I currently have the Starter Plan for Zapier and am using all 20 Zaps. Many of those only run a few times per month. Each week Zapier sends a report on how many Zaps have run. I try to bounce that off how much time they save to make sure it is still cheaper to have the Zap or to do it myself. 

Craig