Automatic Calculation of % Completed and Flagging Capabilities At Risk
I am trying to accomplish the following in our Program View that I am hoping someone can help me with:
- Is there a way to automatically calculate percentage on all lines based on Start & End Dates?
- And if there is what would a formula be for calculating the Progress icon? Would love this to be automatic based on the percentage calculation based on the dates.
- And finally, I am trying to figure out how I could flag capabilities at risk. Thinking maybe calculate the planned % based on the Planned Start/End dates and compare that to the % Completed based on the actual dates? Any ideas would be greatly appreciated!
Thank you!
Best Answer
-
Ashley,
Try this:
=IF(AND([Planned Start]@row = 0, [Planned End]@row = 0), 0, IF(TODAY() < [Planned Start]@row, 0, IF(TODAY() > [Planned End]@row, 1, (TODAY() - [Planned Start]@row) / ([Planned End]@row - [Planned Start]@row))))
It added a statement at the beginning saying that if the planned start AND planned end both equal 0, it should display 0%.
Best,
Heather
Answers
-
Hello @Ashley Johnson ,
Although I was unable to create a Formula to generate percentages based on the Start and End date, I created a Flag Column that would identify if the status was still "In Progress" within 3 days of the End Date.
Please note that this is an example, and the number within the TODAY Function can be changed:#
The Formula I used was =IF(AND(Status@row = "In Progress", End@row <= TODAY(3)), 1, 0).
Please let me know if you have any questions!
Regards
Sean
-
So to answer your questions:
1: That % would be:
=IF(AND(ISDATE([Start Date]@row), ISDATE([End Date]@row)), 1- ([End Date]@row-TODAY())/([End Date]@row - [Start Date]@row),"")
Be aware though, that the result of this would be purely theorical. Projects can be early, late or on time for many reasons that aren't taken into account here.
2: If you want to convert that into progress bar, it'll be a little bit more complex. As progress bar is a string, not a number. So that will require lots of nested ifs.
Let's start again with the above formula.
=IF(AND(ISDATE([Start Date]@row), ISDATE([End Date]@row)), IF([Start date]@row >= TODAY(), "Empty", IF((1 - ([End date]@row - TODAY()) / ([End date]@row - [Start date]@row)) < 0.25, "Empty", IF((1 - ([End date]@row - TODAY()) / ([End date]@row - [Start date]@row)) < 0.5, "Quarter", IF((1 - ([End date]@row - TODAY()) / ([End date]@row - [Start date]@row)) < 0.75, "Half", IF((1 - ([End date]@row - TODAY()) / ([End date]@row - [Start date]@row)) < 1, "Three-Quarters", "Full"))))), "")
3:Actually in your screenshot, planned end date and end dates are the same, so it's not gonna flag lots of things right now.What you have to determine here is whether you want flag project that are late, or those that are longer than expected?
-
Hi Ashley,
1 - Just to clarify - are you wanting your % complete column to display the % of the allotted time (planned start to planned finish) that has passed? If so, you could use a formula something like this:
=(TODAY()-[planned start]@row)/([planned end]@row-[planned start]@row)
This will calculate how many days have passed since the planned start, and divide it by the total planned duration. However, once the planned end date has passed, it will continue to calculate a percentage. So you may want to look at using something like this instead:
=if(TODAY() > [planned end],1,(TODAY()-[planned start}@row)/([planned end]@row-[planned start]@row))
To translate this formula, it says "If the planned end date is in the past, the cell will display 1 (100%). If the planned end date is in the future, the cell will display the % of allotted time that has passed."
2 - The progress icon could be based on the % Complete formula, and it could be something like this:
=IF([% Complete]@row = 1, "Full", IF([% Complete]@row > 0.745, "Three Quarter", IF([% Complete]@row > 0.495, "Half", IF([% Complete]@row > 0.245, "Quarter", "Empty"))))
3 - If you're wanting to compare the % of time that has passed (see formula in #1 - I'm going to call that column [% passed]) to the % complete column, you could create an at risk column with a formula that reads something like this:
=if([% passed] > [% complete], 1,0)
This translates to "If the % of the allotted time that has passed is greater than the % of work that has been completed, flag it." If you wanted to give a little bit of grace - let's say, you allow the % of work to be 10% less than the % time passed without the task being flagged as at risk, you could do something like this:
=if([% passed] > ([% complete]+0.1),1,0)
I hope this helps! If you have any questions, please let me know.
Best,
Heather
-
Hi Heather,
This formula doesn't work but the way you explained it is exactly what I need. Any ideas?
=if(TODAY() > [planned end],1,(TODAY()-[planned start}@row)/([planned end]@row-[planned start]@row))
-
=if(TODAY() > [planned end],1,(TODAY()-[planned start]@row)/([planned end]@row-[planned start]@row))
I noticed that one of the [planned start] ones mistakenly had a curly bracket. Try using the above formula and let me know if it works!
-
Unfortunately, that didn't work either
-
Ashley,
I am perplexed! I have tried it a couple of ways, and it isn't working for me either. I do know that I was missing an @row after the first [planned end], but that still doesn't fix the problem.
I'll keep trying to solve this mystery. I've posted a question for someone to help with the "if the date is in the past" part that is causing problems. I'll let you know what I figure out!
Thanks,
Heather
-
Here we go! Make sure both your Planned Start and Planned End columns are set as date columns in the column properties. From there, use this formula:
=IF(TODAY() > [Planned End]@row, 1, (TODAY() - [Planned Start]@row) / ([Planned End]@row - [Planned Start]@row))
Let me know if it works!
-
Also - I noticed that it will show a -% if a planned start date is in the future. To solve this, you can do one of the following:
To have it display a blank field for rows when the planned start is in the future, use this:
=IF(TODAY() < [Planned Start]@row, "", IF(TODAY() > [Planned End]@row, 1, (TODAY() - [Planned Start]@row) / ([Planned End]@row - [Planned Start]@row)))
To have it display 0% for rows when the planned start is in the future, use this:
=IF(TODAY() < [Planned Start]@row, 0, IF(TODAY() > [Planned End]@row, 1, (TODAY() - [Planned Start]@row) / ([Planned End]@row - [Planned Start]@row)))
-
That worked!!! Thank you!
-
Heather,
One more question: Is there a way to do 0% if there are no plan dates?
-
Ashley,
Try this:
=IF(AND([Planned Start]@row = 0, [Planned End]@row = 0), 0, IF(TODAY() < [Planned Start]@row, 0, IF(TODAY() > [Planned End]@row, 1, (TODAY() - [Planned Start]@row) / ([Planned End]@row - [Planned Start]@row))))
It added a statement at the beginning saying that if the planned start AND planned end both equal 0, it should display 0%.
Best,
Heather
-
Ashley,
My apologies; I could have sworn I answered this question!
If you want it to display 0% if there are no dates, use this:
=IF(AND([Planned Start]@row = 0, [Planned End]@row = 0), 0, IF(TODAY() < [Planned Start]@row, 0, IF(TODAY() > [Planned End]@row, 1, (TODAY() - [Planned Start]@row) / ([Planned End]@row - [Planned Start]@row))))
If you want it to display a blank cell if there are no dates, use this:
=IF(AND([Planned Start]@row = 0, [Planned End]@row = 0), "", IF(TODAY() < [Planned Start]@row, 0, IF(TODAY() > [Planned End]@row, 1, (TODAY() - [Planned Start]@row) / ([Planned End]@row - [Planned Start]@row))))
Best,
Heather
-
Yupp you did Heather! I made it the Accepted Answer =) If you have any thoughts on this new question let me know. You have been SUPER helpful!
-
Aha - THAT's where it went! :) Happy to help.
I was actually just getting ready to look at that one. Challenge accepted!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
