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!

Best Answer

  • ChristianFinke
    ChristianFinke ✭✭✭✭✭
    Answer ✓

    @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!

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

Answers

  • ChristianFinke
    ChristianFinke ✭✭✭✭✭
    Answer ✓

    @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!

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

  • Jill Clay
    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
    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!

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!