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?

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?

Find more posts tagged with

Accepted answers

All comments

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!

Best,

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!

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.