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

Options
✭✭✭

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?

• ✭✭✭
Options

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

• ✭✭✭✭✭
Options

Hello,

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

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

• ✭✭✭
Options

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

• ✭✭✭✭✭
Options

try this:

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

if the dates are empty it will just keep it blank

• ✭✭✭
Options

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

• ✭✭✭✭✭
Options

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,

• ✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭
Options

I'm glad worked out for you.

Regards

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
edited 06/29/21
Options

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

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Excellent!

Happy that you figured it out!

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.

• ✭✭✭
Options

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

• ✭✭✭
Options

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!