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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 01/06/21

    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.

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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.

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    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)

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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.

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Yes, that worked. Thanks

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

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

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Disregard! I figured it Out

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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!