Formula Date Remaining Static

This is a good question. Also, I did not know you could put a number as an argument for the TODAY() function to add (or subtract, it turns out) days from "today," so thanks for sharing!

I understand what you're trying to achieve here, but I cannot think of a way to get this done by just editing the formula you mention. A little more horsepower is needed here, I do believe.

One option (this is what we do) is to have a milestone task after each one of these items (with the standard FS predecessor relationship), and send a reminder based on the start date of the milestone (which will always be fixed to one day after the end date of a task). However, if you don't have dependencies enabled, or you don't have complete on dates for the tasks you're talking about, this won't work. Besides, you'll have to add the milestone relationship for every task across 30 sheets.

What I think you're really looking for is some kind of one-shot trigger that gets pulled just once and then is not reset later on. I'm racking my brain to think of a mechanism in Smartsheet that can do this, but I don't have anything right now. Perhaps some other users know of a way to set off a one-shot type of trigger. Having something like that would probably get us pretty close to solving your problem.

Possible workaround (involves minimal, but manual effort):

Up front effort:

• 1. Add a date-type column called "Copy Date"
• 2. Make a report that shows these columns: Primary (if you want), Status, Status Complete, Copy Date
• 3. Repor parameters: Status=Complete, Copy Date is not a date
• 4. Make all of your reminders based on your new column "Copy Date"

Daily effort:

• 5. Once each day copy everything the values from the Status Complete to the Copy date column.
• 6. After you save and refresh the report, these rows will be gone, and you'll have nothing to do until tomorrow. When you open it up tomorrow, any new tasks which have been complete will show up and you'll knock them down in the same way.

Obvioulsy, you'll probably want to change the column names. A possible time saver to implementation  might be to move your existing formula to the new column you'd create, then blank out the "Status Complete" column. Your initial adjustment in the report will be massive, likely, but a one-time thing.

We do work similar to this, and even though it's not fully automatic, it takes only a few clicks and a few seconds to do what amounts to a really tremendous amount of work.

Good suggestion, Travis. That will work, of course, but my thought was that the completion date was something worth preserving.

For anyone else looking for a one-shot trigger solution which works in situations similar to that above, here's what I did.

1. First, I added the system column "Modified Date" to allow for Smartsheet to track the last time that row was modified. In this case, it would have been when the Status column's value was changed to complete(assuming no other changes were made/updated in the row. One way to do this is to lock the row). 
   
   
3. Following that, I added a column for the date, in my case Closed Out Date, which simply checks whether a condition is met, and if it is, copy the modified date. it looks like this; =IF([Closed Out]1, Modified1).;That way I copy that static date there assuming the condition was met.

In the above situation, the column which the reminder is based on, which is "Status Complete" would simply have the formula =IF(Status1="Complete", Modified1). This way you won't have a date value where there is no "Complete" Status.

This solution works especially well where there is only one sheet administrator, and fits situations where there is no content change after the status has been changed to complete.

All the best,

Chris.

Craig,

I would like to do this with Zapier, but I am having difficulties setting this up.  Does this still work for you?  When I am creating the Zap at the Trigger level under Edit Options, I choose my sheet and there is a button Refresh Fields which I have hit numerous times, but it doesn't load the fields so I can pick the field I want to use as the trigger.  Would appreciate your help!

Thanks!

Brenda