SUM "Duration" based on date Range
Answers
-
Which column are you using for your date range?
-
The End Date column
-
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 =.
-
Ah, thanks!
-
Changed Complete to Scheduled and it worked. Thanks
-
Great! Happy to help! 👍️
-
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.
-
How did you try adding it? The IFERROR should have worked.
-
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"))
-
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)
-
Mind...🤯...Blown!
-
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.😀
-
Glad you got it working! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!