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
-
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.
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)
-
Hi, I get a "Divide By Zero" error. The Column is formatted for %
-
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:
-
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 :)
-
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!
-
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
-
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.
-
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
-
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.
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!