Issue tracker: Average Days to Resolve based on a time calculation on another range

Mike_Smith

I have an issue tracker sheet that includes SubmittedOn (Date) and ResolvedOn (Date) columns. I created a DaysToResolve column that counts the NETWORKDAYS (SubmittedOn, ResolvedOn). I am trying to build a dashboard that shows the Average DaysToResolve issues created this week, last week, etc. I have tried AVERAGEIF, SUMIF/COUNTIF, etc. Something like: =AVERAGEIF({DaysToComplete}, ({ResolvedOn}, @cell >= (today()-weekday(today())-6))). Everything I have tried results in #Unparseable or #Incorrect Argument. I have looked at dozens of other questions and answers and haven't found anything that would work for this situation. Sure wish I could just write my own query!

Paul Newcome

Try something along the lines of...

=AVG(COLLECT({DaysToComplete}, {ResolvedOn}, @cell >= TODAY() - WEEKDAY(TODAY())-6))

Paul Newcome

Try something along the lines of...

=AVG(COLLECT({DaysToComplete}, {ResolvedOn}, @cell >= TODAY() - WEEKDAY(TODAY())-6))

Mike_Smith

That worked! Posting the full formula I used for the benefit of the community:

=AVG(COLLECT({DaysToResolve}, {SubmittedOn}, @cell < TODAY() - WEEKDAY(TODAY()) + 1, {SubmittedOn}, @cell >= TODAY() - WEEKDAY(TODAY()) - 6, {Resolved}, 1, {ResolvedOn}, @cell < TODAY() - WEEKDAY(TODAY()) + 1, {ResolvedOn}, @cell >= TODAY() - WEEKDAY(TODAY()) - 6))

I'm averaging DaysToResolve for issues that were both submitted and resolved during the last calendar week.

Thank you!

Paul Newcome

Happy to help. 👍️