Formula Date Remaining Static

CraigH

I have aprroximately 30 sheets related to a corporate merger. In every sheet I have a formula that populates today's date +1 whenever a status column value = Complete. The formula is:

=IF(Status2 = "Complete", TODAY(1))

 

This then triggers a reminder to a supervisor to review the task and approve its completion.

 

So, the person assigned the task changes the status to "Complete" today, July 8. This triggers the formula in another column called "Status Complete" to return tomorrow's date, July 9. On July 9, the reminder email is sent to the supervisor.

 

However, if I am not mistaken, for as long as the status = "Complete", the "Status Complete" date will change and reminders will keep being sent. Is there a way to change the formula so that the status complete date does not keep changing?

 

Thanks.

John Sauber

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.

 

 

 

CraigH

Thanks for the feedback, John. Yes, a "one-shot trigger" is what I need but I don't believe it's available right now.

Of course, reminders that are not date-based would certainly help but I'm not sure where they may be on the Smartsheet development cycle.

John Sauber

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.

 

Travis

How does the supervisor approve the completion of a task? If they change something in the sheet, you can use that as a trigger to remove the date from the Status Complete column. For example if the supervisor checks a box in an “Approved” column, you could use this formula:

 

 

=IF(Approved2 = 1, “”, IF(Status2 = "Complete", TODAY(1))

John Sauber

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

CraigH

Travis and John,

 

Thanks for your help with this.

 

To help clarify I am sharing a screenshot of one of the sheets. You can see the formula I now have in the "Status Complete" column.

 

1. The person "Assigned To" the task would change the "Status" to "Complete"

2. The formula in the "Status Complete" column would return tomorrow's date.

3. This would trigger a reminder to the supervisor to review the work.

4. If the supervisor is satisfied the work has been completed, they would check the box in the "Complete Status Approved" column and this would remove the date from the "Status Complete" column, thereby stopping the reminders.

 

The only "glitch" with this is if the supervisor takes 2 or 3 days to review and approve the work, they would get a reminder about the same task every day until they check the box. I have justified this as a way to annoy them until they review the work 

Christopher Derrell

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.

 

J. Craig Williams

I do this now using Zapier (www.zapier.com)

 

I have a Zap that watches for an empty date column and a completion check box - when the check box is checked by the user, today's date is entered into the date column.

Later, if someone makes another change elsewhere in the row, which triggers the Zap, it does not do anything because the date column is not empty.

 

 

Craig

bms-amhi

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

J. Craig Williams

Brenda,

That sounds like there has not been a recent row added on your sheet.

Here's the post I wrote that discusses my solution:

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

Craig