SUM "Duration" based on date Range

Joel Brumback

Paul Newcome

Which column are you using for your date range?

Joel Brumback

The End Date column

Paul Newcome

Your formula is looking for "not equal to Complete". That's what the <> means. It is the opposite of =.

The formula I provided as an example was for "tasks that haven't yet been completed..." to show the average duration of outstanding tasks. If you want to show the average duration of completed tasks, you will want to change <> to =.

Joel Brumback

Ah, thanks!

Joel Brumback

Changed Complete to Scheduled and it worked. Thanks

Paul Newcome

Great! Happy to help! 👍️

Joel Brumback

I am going to test your patience as well as your talent. What if I wanted to show a "0" when the result comes back #DIVIDE BY ZERO? When there is no completed jobs the result is #DIVIDE BY ZERO and I would rather see 0. It looks nicer in my dashboard. I tried adding an IFERROR but it didn't work.

Paul Newcome

How did you try adding it? The IFERROR should have worked.

Joel Brumback

Looking for a good laugh are you? lol I am not good with advanced nested formulas, yet.

=AVG(COLLECT({Drop Bury Tracker Range 7}, {Drop Bury Tracker Range 6}, AND(@cell >= DATE(2020, 5, 18), @cell <= DATE(2020, 5, 24)), {Drop Bury Tracker Range 2}, "RICH BONK", {Drop Bury Tracker Range 4}, @cell <> "SCHEDULED")), IFERROR([Days To Complete]:[Days To Complete], @cell "0"))

Paul Newcome

Not at all. You can't know something until you learn it.

Try this...

=IFERROR(original_formula, 0)

=IFERROR(AVG(COLLECT({Drop Bury Tracker Range 7}, {Drop Bury Tracker Range 6}, AND(@cell >= DATE(2020, 5, 18), @cell <= DATE(2020, 5, 24)), {Drop Bury Tracker Range 2}, "RICH BONK", {Drop Bury Tracker Range 4}, @cell <> "SCHEDULED")), 0)

Joel Brumback

Mind...🤯...Blown!

Joel Brumback

Is there a way to not have to enter the date for each cell and maybe reference the week start date and week end date? In excel I would use a cell reference like $S2 and $S3 but when I added [Week Start Date]2 and [Week End Date]2 it gave me an error.

EDIT: Never mind, I got it to work.😀

Paul Newcome

Glad you got it working! 👍️