Actual % Complete vs Expected % Complete
Hey! Please, does anyone know how I can calculate the "Expected % Complete?" I have tried numerous formulas but it is not giving me the outcome that is needed. Will I need to use the "Duration" or the "Start Date" or "End Date" in any particular order?
The Formula I use to find Expected % Completed - =IFERROR(ROUND((TODAY() - [Start Date]@row) / [End Date]@row - [Start Date]@row), 2), "")
Best Answers
-
Give this a try:
=MAX(MIN((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row), 1), 0)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
How about this?
=IF(NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row) > 1, 1, NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row))
If you wan to exclude holidays, I have a separate sheet with holidays and I reference that in the networkdays formula.
-
Hey! thank you for your help. Unfortunately, it is still not properly giving me the expected % complete of each task. It shows mostly "1" which does not translate to anything.
-
The logic said if my end date is < today then make the expected to be 1 or 100%. Id change the column to a %. I assumed that if you were past the End date the expected is that you'd be 100% complete.
-
Thank you for the explanation. Let me be more detailed in what I am asking.
1) I need a formula that calculates the scheduling status by analyzing a work item's current Actual Percent Completed versus the defined Expected Percent Completed. I assume that "Duration" plays a part in calculating when a particular task is expected to be complete (automatic input) as opposed to the actual % completed (manual).
2) I tried changing the column to a % as you suggested but that it went above the 3-digit number. I even changed the "End date" to "2025" just to see the outcome but I do not believe that caused a major change.
Please, let me know if you need more clarity or perhaps I am doing something awfully wrong
-
Give this a try:
=MAX(MIN((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row), 1), 0)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It Worked!! Thanks Paul 😊
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!