How to check a box if a date is in the past 7 days & next 14


I want to check a box if the following criteria are met. I want to then base an automation to send out rows that are not complete and were due in the last 7 days / due in the next 14.


Status is not Complete

Finish is in the past 7 days (overdue)

Finish is in the next 14 (look forward 2 weeks)

This is the formula I tried - and is obviously incorrect. Any guidance would be appreciated!

=IF((Status:Status<> "Complete", And(Finish:Finish <=TODAY(-7)),Finish:Finish <TODAY(+14),1,0)



  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Matthew_Lanterman

    I hope you're well and safe!

    Not completely sure I follow but try something like this.

    =IF(AND(Status@row = "", Finish@row = ""), "", IF(AND(Status@row <> "Complete", Finish@row <= TODAY(-7), Finish@row < TODAY(14)), 1))

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Matthew_Lanterman
    Matthew_Lanterman ✭✭✭✭✭

    Hi @Andrée Starå - thank you for the help.

    The goal is to surface all tasks that are incomplete within a timeframe (measuring from today, I need lines that are not complete and were due the past 7 days and due in the next 14).

    Alternatively, I could write conditions in an automation that might accomplish the same goal:

    That way I would not need a formula, and can use the conditions in an automation. Do you think that would work?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!