SUM "Duration" based on date Range
Answers
-
Which column are you using for your date range?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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 =.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Ah, thanks!
-
Changed Complete to Scheduled and it worked. Thanks
-
Great! Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!