Setting up Automation correctly - TODAY()

Richard Heath

I am using a To-Do-List in Smartsheet that works well. As new tasks come to hand, I drop them on the list for future action. I have a simple automation that emails me at 8am for any tasks that have a Due date today (and not already completed).

What I would like is for the Automation to email me for all activities that are Due today AND those that were due prior to today but not yet marked complete. That way I get a consolidated list of all outstanding activities.

Two possible solutions:

1. Reconfigure Automation, but I can't see how I can do that
2. Create a new column with a IF(AND) formula that compares the Complete Column to the Due Date column and applies a <=today() formula.

Any thoughts would be appreciated.

If I can get it to work, I should be getting a list containing only items 2 and 4 above (Item 1 is in the future) at time of writing.

Kevin Smith

Hi,

Option #1 is possible; use something like this should work:

It assumes that you don't want ANY notification regardless of due date if it is complete. If that doesn't work there is probably another way.

Option #2 will work; set up the formula to be used as you stated in a helper col. that changes daily.

I hope that helps.

Richard Heath

Thank you both for your answers.

I've implemented Kevin's automation solution. Hopefully, this will resolve the issue, and tomorrow I'll receive a list of outstanding actions.

Kelly Moore

Hey @Richard Heath

One approach, as you indicated, is to use a helper column. You can use a checkbox column for this

Are you wanting all actions that are due today regardless if they are complete or not? I'll give you two formulas, one that has all of Today's actions regardless of Complete plus your past dues, and one that only includes the not complete actions.

All Today (and past dues)

=IF(OR(Due@row=TODAY(), AND(Due@row<TODAY(), Complete@row=0)), 1)

Or

Only Incompletes

IF(AND(Due@row<=TODAY(), Complete=0), 1)

Does this work for you?

Kelly

Kevin Smith

Hi,

Option #1 is possible; use something like this should work:

It assumes that you don't want ANY notification regardless of due date if it is complete. If that doesn't work there is probably another way.

Option #2 will work; set up the formula to be used as you stated in a helper col. that changes daily.

I hope that helps.

Richard Heath

Thank you both for your answers.

I've implemented Kevin's automation solution. Hopefully, this will resolve the issue, and tomorrow I'll receive a list of outstanding actions.