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

Todd Smelser

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?

Todd Smelser

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.

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)

Todd Smelser

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

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

Todd Smelser

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

Screen Shot 2021-06-10 at 6.01.59 PM.png

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

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

Todd Smelser

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

Alex Argumedo

I'm glad worked out for you.

Regards

Todd Smelser

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å

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!

Todd Smelser

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

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å

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

Todd Smelser

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

Ryan Finney

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