Assignee reporting for all tasks during specific date ranges

I have been trying to create a report that shows all the tasks my team have on each week. I found some guidance in a community post about setting filters based on the start and end date. However I have found this doesn’t provide quite what I need so I was wondering if someone might be able to help me.

Basically, I want to see all the tasks my team are assigned to on any given week, so I can keep an eye on team capacity and see if there is anything I might need to re-assign to someone else. So for example I would want to be able to look at this report and see anything my team are working on, if activity takes place during that Monday-Friday period.

I have created a report so far that looks at all the project plans for my team, pulls through columns for the Task Description, the Sheet Name link, Start Date, End Date and Status. I have set filters for Assignee is not blank, and Status is not complete. The report is then grouped by Assignee so I can look at each team member’s workload.

The previous post I found helped me narrow the time period using additional filters for start and end dates being <= or >= the Monday and Friday. But I have found this will only pull out tasks that start and/or end within that Monday to Friday. In the visual example below, if I was looking at Week 2, these would be the green-coloured tasks B, C, E, G and H. It doesn’t include any activity where the start and/or end date falls outside that window – in the week I’m looking at, I would also want the report to pull through Task A, Task D and Task F.

Can anyone help me understand how I can set up the report to include all activity taking place in a set time period, not just those that start/end within that period?

Similarly, If I looked at the report the week after, looking at Week 3, upon changing the dates of the time period, I would expect the report to only show Task D, Task F, Task I and Task J (based on the prior tasks being complete – if any were extended into Week 3, I would expect these to show up too)

I hope that makes sense, but if I can clarify anything to help get to an answer, please let me know.

Thank you in advance if someone is able to help me!

Find more posts tagged with

Formulas

Reports

Solution Center

Project Management

Resource Management

Accepted answers

ChristianFinke

@Jill Clay

To capture tasks that span any part of a given week, set up filters to include tasks where the Start Date is on or before the week’s end (Friday) and the End Date is on or after the week’s start (Monday). This way, your report will pull in all tasks that overlap with that week, even if they don’t specifically start or end within it. Just update these date filters each week to reflect the new timeframe. Let me know if that helps or if you need further clarification!

Jill Clay

Hi @Carla C,

'Is on or before' isn't an option… based on Christian's feedback, I had to set two filters each for start date and end date, and use OR and AND functions to make it calculate correctly

E.g.

Start date - set two filters, 'is equal to' OR 'is not greater than' the end of the week

End date - set two filters, 'is equal to' OR 'is greater than' the start of the week

Then AND the start and end pairs of filters (see screenshot below). I also included and AND function to add additional filters for assignee and status (not in the screenshot).

Hope that helps!

All comments

ChristianFinke

Jill Clay

Hi @ChristianFinke

Thank you so much - this has worked and is showing me exactly what I need now!

I think I got confused using the filters as the previous post I mentioned in my question had 4 different 'less than or equal to' and 'greater than or equal to' filters for both the Start Date and End Date, and they were the opposite way round to the way you've suggested (i.e. the Start Date filters were looking at the Monday date and the End Date filters were looking at the Friday date). No matter how I tried to amend and reverse engineer it, I couldn't get it to give me the right information.

But I've just set this up to look at next week, and I now have a manageable list of only the activities that sit across or during the week from next Monday to Friday 👍️

Thank you again for your help!

ChristianFinke

@Jill Clay

Happy to hear! Let me know if you have any further questions or solutions you'd like built out in the future!

Carla C

I am trying to do this same thing, our tasks can take multiple sprints based on size. When I try to add filters for the dates, 'is on or before' and 'is on or after' are not options on the drop down list. Is this using the calendar app or can it be done from a report?

Thank you for reading!

Jill Clay

Hi @Carla C,

'Is on or before' isn't an option… based on Christian's feedback, I had to set two filters each for start date and end date, and use OR and AND functions to make it calculate correctly

E.g.

Start date - set two filters, 'is equal to' OR 'is not greater than' the end of the week

End date - set two filters, 'is equal to' OR 'is greater than' the start of the week

Then AND the start and end pairs of filters (see screenshot below). I also included and AND function to add additional filters for assignee and status (not in the screenshot).

Hope that helps!

Carla C

Hi Jill,

Thank you so much for clarifying! It works and I am so happy!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.