Am I able to create a formula that will advance the percentage complete based on start and End Date?

I am looking to automatically advance the Percentage complete calculated against the Start and End date with today's date as a variable.

Is it possible to Calculate Duration (3Days) and use the first day as the criteria to automatically reflect 30% complete based on the start date?

Best Answer

  • Todd Smelser
    Todd Smelser ✭✭✭
    Answer ✓

    I may have figured it out.

    =IFERROR(IF(ISBLANK(Start@row), "", IF(TODAY() > Finish@row, 1, IF(TODAY() < Start@row, 0, IF(AND(TODAY() > Start@row, TODAY() < Finish@row), ((1 / (Finish@row - Start@row)) * (Finish@row - TODAY()) / 1), IF(TODAY() > Start@row, 1, IF(TODAY() < Finish@row, 0, "")))))), "")

    Seems to sound in the correct direction.

«1

Answers

  • Alex Argumedo
    Alex Argumedo ✭✭✭✭

    Hello,

    Try this, make sure you change the format of the cell or column to %,

    =((100 / (enddate@row - startdate@row)) * (enddate@row - TODAY()) / 100)

  • Hi, I get a "Divide By Zero" error. The Column is formatted for %

  • Alex Argumedo
    Alex Argumedo ✭✭✭✭

    try this:

    =IFERROR((100 / (enddate@row - startdate@row)) * (enddate@row - TODAY()) / 100, "")


    if the dates are empty it will just keep it blank

  • Hi, Thank you so much for your work! Seems to work Ok but I am getting some anomalies on some rows like this:


  • Alex Argumedo
    Alex Argumedo ✭✭✭✭

    Remember that the formula considers today's date in the calculation, and both those dates are in the future hence the error.

    if you have dates like that in the columns the formula will fail as it considers today's date. you can add a whole new nested formula to evaluate the start and end dates to check if todays is not between both and leave it as 0%.

    I think you will also see this in dates in the past which can also fixed with additional nested formulas

    I hope this makes sense,

    If you consider this an answer please accept it :)

  • Alex Argumedo
    Alex Argumedo ✭✭✭✭

    I worked some exceptions,

    If the start-date or end-date is empty it will stay blank

    if the start date is in the future it will show 0% (not started yet)

    if the end date is is the past it will show 100% (done)

    =IFERROR(IF(ISBLANK(startdate@row), "", IF(TODAY() > enddate@row, 1, IF(TODAY() < startdate@row, 0, IF(AND(TODAY() > startdate@row, TODAY() < enddate@row), ((100 / (enddate@row - startdate@row)) * (enddate@row - TODAY()) / 100), IF(TODAY() > startdate@row, 1, IF(TODAY() < enddate@row, 0, "")))))), "")

  • That seems to work very well. Thank you so much!

  • Alex Argumedo
    Alex Argumedo ✭✭✭✭

    I'm glad worked out for you.

    Regards

  • Hi Alex,

    Ok, I am confused again. I have two project plans with which I am working. The Start and End Date column are identical as are the other columns in the sheet. Your formula works great in the first sheet but, when I copy it to the second sheet, I get "UNPARSEABLE"

    =IFERROR(IF(ISBLANK(Start@row), "", IF(TODAY() > Finish@row, 1, IF(TODAY() < Start@row, 0, IF(AND(TODAY() > Start@row, TODAY() < Finish@row), ((100 / (Finish@row - Start@row)) * (Finish@row - TODAY()) / 100), IF(TODAY() > Start@row, 1, IF(TODAY() < Finish@row, 0, ""))))))),

    I have tried relinking the start and end dates@row directly but still get UNPARSEABLE

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @TSmelser

    I hope you're well and safe!

    Did you include the last comma in the formula?

    Try this one.

    =IFERROR(IF(ISBLANK(Start@row), "", IF(TODAY() > Finish@row, 1, IF(TODAY() < Start@row, 0, IF(AND(TODAY() > Start@row, TODAY() < Finish@row), ((100 / (Finish@row - Start@row)) * (Finish@row - TODAY()) / 100), IF(TODAY() > Start@row, 1, IF(TODAY() < Finish@row, 0, "")

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Todd Smelser
    Todd Smelser ✭✭✭
    edited 06/29/21

    Hi,

    Sorry to take so long to get back to you. I have put this formula into action and it is counting in the wrong direction. It seems to start at 100 and count down as the end date gets closer to today. I have tried reversing the logic in the formula but it errors out.

    IFERROR(IF(ISBLANK(Start@row), "", IF(TODAY() > Finish@row, 1, IF(TODAY() < Start@row, 0, IF(AND(TODAY() > Start@row, TODAY() < Finish@row), ((100 / (Finish@row - Start@row)) * (Finish@row - TODAY()) / 100), IF(TODAY() > Start@row, 1, IF(TODAY() < Finish@row, 0, ""))))))),


    Best,

    Todd

  • Todd Smelser
    Todd Smelser ✭✭✭
    Answer ✓

    I may have figured it out.

    =IFERROR(IF(ISBLANK(Start@row), "", IF(TODAY() > Finish@row, 1, IF(TODAY() < Start@row, 0, IF(AND(TODAY() > Start@row, TODAY() < Finish@row), ((1 / (Finish@row - Start@row)) * (Finish@row - TODAY()) / 1), IF(TODAY() > Start@row, 1, IF(TODAY() < Finish@row, 0, "")))))), "")

    Seems to sound in the correct direction.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @TSmelser

    Excellent!

    Happy that you figured it out!

    Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi, well I was wrong...my formula still counts in the wrong direction

  • Ryan Finney
    Ryan Finney ✭✭✭

    Any update to the above formula? It is working as described but is counting DOWN to completion instead of UP to completion.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!