# SUM "Duration" based on date Range

Options
2»

• Options
• ✭✭✭✭✭✭
Options

Which column are you using for your date range?

• Options

The End Date column

• ✭✭✭✭✭✭
Options

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 =.

• Options

Ah, thanks!

• Options

Changed Complete to Scheduled and it worked. Thanks

• ✭✭✭✭✭✭
Options

Great! Happy to help! 👍️

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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"))

• ✭✭✭✭✭✭
Options

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)

• Options

Mind...🤯...Blown!

• edited 06/18/20
Options

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.😀

• ✭✭✭✭✭✭
Options

Glad you got it working! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!