Looking for formulae help with date/task capture.


I'm creating a current project/task sheet and I need some help with formulas to set up some automation for reporting.

I have 3 sets of data that I would like to look at on a weekly basis

  • Tasks completed in the current 7-day period - as set by the date they had their status changed to complete.
  • Tasks covering the next 7-day period - as set by their expected completion date
  • Current outstanding/overdue tasks - as set by their expected completion date versus the current date

I'd like to be able to take any tasks that fall into these 3 categories and copy their row(s) into a new sheet which can then be forwarded in an email format to designated people.

I've been looking through various threads trying to get to the bottom of how to do this, but maybe it's been my search criteria but I can't seem to narrow down exactly how I would go about doing this - can someone please help?


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

    Hi @Simon Russell

    I hope you're well and safe!

    Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to 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: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Simon Russell

    Hey @Andrée Starå

    Hope this helps?

    So my plan would be to have these automated processes run every friday.

    So for the 7 days running upto whichever date we're on for that particular Friday, any task that had it's completion date within those 7 days I want to copy to a new sheet along with any tasks that have their Due Date fall within the next 7 days and then finally any rows that have the Deadline (days) sitting in a negative number.

    If I could move any/all of those into a new sheet and then email that out, this would be the desired outcome.

    I'll be honest, haven't got the foggiest where to start!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!