Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    ✭✭✭
    in Formulas and Functions

    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?

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

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

      · Share on FacebookShare on Twitter
    «1

    Answers

    • ✭✭✭✭✭

      Hello,

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

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

      · Share on FacebookShare on Twitter
    • ✭✭✭

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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      try this:

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


      if the dates are empty it will just keep it blank

      · Share on FacebookShare on Twitter
    • ✭✭✭

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

      Image
      Screen Shot 2021-06-10 at 6.01.59 PM.png
      Uploaded 11.32 KB


      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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, "")))))), "")

      · Share on FacebookShare on Twitter
    • ✭✭✭

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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      I'm glad worked out for you.

      Regards

      · Share on FacebookShare on Twitter
    • ✭✭✭

      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

      · Share on FacebookShare on Twitter
    • Community Champion

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭
      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

      · Share on FacebookShare on Twitter
    • ✭✭✭
      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.

      · Share on FacebookShare on Twitter
    • Community Champion

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

      · Share on FacebookShare on Twitter
    • ✭✭✭

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

      · Share on FacebookShare on Twitter
    • ✭✭✭

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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions