Setting up Automation correctly - TODAY()

Options
Richard Heath
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.

Tags:

Best Answers

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Richard Heath
    Richard Heath ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Kevin Smith ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Richard Heath
    Richard Heath ✭✭✭✭✭✭
    Answer ✓
    Options

    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.