# Duration and % Percent Formula

Options
✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
edited 01/06/21
Options

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.

• ✭✭✭✭✭✭
Options

Thanks for trying Mark but that did not work. Results were UNPARSABLE

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Yes, that worked. Thanks

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Disregard! I figured it Out

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!