Skip to content
  • Try for free

    • Smartsheet Basics

    Smartsheet Basics

    Ask questions about the core Smartsheet application: Sheets, Forms, Reports, Dashboards, and more.

    Setting up Automation correctly - TODAY()

    Community Champion
    in Smartsheet Basics

    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.

    image.png


    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:
    · Share on FacebookShare on Twitter

    Sign in
    Try for free

    Best Answers

    • ✭✭✭✭✭✭
      Answer ✓

      Hi,

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

      image.png

      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)

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      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.

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      Answer ✓

      Hi,

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

      image.png

      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)

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      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.

      · Share on FacebookShare on Twitter

    Sign in
    Try for free

    Quick Links

    Categories

    Trending in Smartsheet Basics

    Help shape the future of Smartsheet.
    Share your ideas and feature requests.

     Watch video