Duration and % Percent Formula
Smartsheet Community:
Is there any tweaks I can make to this formula so when I put in 1h the Result is not 800% or 0.5h does not Result in 1,600%?
=IFERROR(IF(AND(TODAY() >= [Start Date (Target)]23, TODAY() <= [Finish Date (Target)]23), ROUND(NETWORKDAYS([Start Date (Target)]23, TODAY()) / Duration23, 2), IF(TODAY() > [Finish Date (Target)]23, 1, 0)), 0)
Best Answer
-
I think I found the error. Try this:
=IFERROR(IF(AND(Duration@row < 1, [Start Date (Target)]@row = TODAY()), Duration@row / 8, IF(AND(TODAY() >= [Start Date (Target)]@row, TODAY() <= [Finish Date (Target)]@row), ROUND(NETWORKDAYS([Start Date (Target)]@row, TODAY()) / Duration@row, 2), 1)), 0)
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi @Ray B ,
I am far from an expert on Duration issues, but try this:
=IFERROR(IF(AND(Duration@row < 1, [Start Date]@row = TODAY()), duration@row / 8, (IF(AND(TODAY() >= [Start Date (Target)]23, TODAY() <= [Finish Date (Target)]23), ROUND(NETWORKDAYS([Start Date (Target)]23, TODAY()) / Duration23, 2), IF(TODAY() > [Finish Date (Target)]23, 1, 0))), 0)
In your formula is looks like the error occurs when the start date is today and the duration is less than a full day. This formula will give you a value less than 100% until the day changes and it goes to 100%. You might want to just report it as complete so instead of duration@row/8 you'd use 1.
What you needed?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thanks for trying Mark but that did not work. Results were UNPARSABLE
-
That means I misnamed one of your columns. Sorry. Try:
=IFERROR(IF(AND(Duration@row < 1, [Start Date (Target)]@row = TODAY()), Duration@row / 8, (IF(AND(TODAY() >= [Start Date (Target)]@row, TODAY() <= [Finish Date (Target)]@row), ROUND(NETWORKDAYS([Start Date (Target)]@row, TODAY()) / Duration@row, 2), IF(TODAY() > [Finish Date (Target)]@row, 1, 0))), 0)
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Received Incorrect
=IFERROR(IF(AND(Duration@row < 1, [Start Date (Target)]@row = TODAY()), Duration@row / 8, (IF(AND(TODAY() >= [Start Date (Target)]@row, TODAY() <= [Finish Date (Target)]@row), ROUND(NETWORKDAYS([Start Date (Target)]@row, TODAY()) / Duration@row, 2), IF(TODAY() > [Finish Date (Target)]@row, 1, 0))), 0)
-
I think I found the error. Try this:
=IFERROR(IF(AND(Duration@row < 1, [Start Date (Target)]@row = TODAY()), Duration@row / 8, IF(AND(TODAY() >= [Start Date (Target)]@row, TODAY() <= [Finish Date (Target)]@row), ROUND(NETWORKDAYS([Start Date (Target)]@row, TODAY()) / Duration@row, 2), 1)), 0)
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Yes, that worked. Thanks
-
@Mark Cronk The Formula is Yielding 200% when the Duration is 0.5 Days. Any Idea on how to prevent this when using half days?
=IFERROR(IF(AND(TODAY() >= [Start Date]20, TODAY() <= [Finish Date]20), ROUND(NETWORKDAYS([Start Date]20, TODAY()) / [No. Of Days]20, 2), IF(TODAY() > [Finish Date]20, 1, 0)), 0)
-
Disregard! I figured it Out
-
Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
Check out the Formula Handbook template!