Duration and % Percent Formula

01/06/21
Answered - Pending Review

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)

Answers

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

    Hi @Ray B ,

    I am far from an expert on Duration issues, but try this:

    =IFERROR(IF(AND([email protected] < 1, [Start Date]@row = TODAY()), [email protected] / 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 [email protected]/8 you'd use 1.

    What you needed?

    Mark

  • Ray BRay B ✭✭✭✭✭

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

  • Mark CronkMark Cronk ✭✭✭✭✭

    That means I misnamed one of your columns. Sorry. Try:

    =IFERROR(IF(AND([email protected] < 1, [Start Date (Target)]@row = TODAY()), [email protected] / 8, (IF(AND(TODAY() >= [Start Date (Target)]@row, TODAY() <= [Finish Date (Target)]@row), ROUND(NETWORKDAYS([Start Date (Target)]@row, TODAY()) / [email protected], 2), IF(TODAY() > [Finish Date (Target)]@row, 1, 0))), 0)

    Mark

  • Ray BRay B ✭✭✭✭✭

    Received Incorrect

    =IFERROR(IF(AND([email protected] < 1, [Start Date (Target)]@row = TODAY()), [email protected] / 8, (IF(AND(TODAY() >= [Start Date (Target)]@row, TODAY() <= [Finish Date (Target)]@row), ROUND(NETWORKDAYS([Start Date (Target)]@row, TODAY()) / [email protected], 2), IF(TODAY() > [Finish Date (Target)]@row, 1, 0))), 0)

  • Mark CronkMark Cronk ✭✭✭✭✭

    I think I found the error. Try this:

    =IFERROR(IF(AND([email protected] < 1, [Start Date (Target)]@row = TODAY()), [email protected] / 8, IF(AND(TODAY() >= [Start Date (Target)]@row, TODAY() <= [Finish Date (Target)]@row), ROUND(NETWORKDAYS([Start Date (Target)]@row, TODAY()) / [email protected], 2), 1)), 0)

    Work?

    Mark

Sign In or Register to comment.