% Complete based on Todays date, Start and End dates
I'm looking to return a % complete based on the start and end dates and the current date.
So if a task starts on the 17/7 and ends on the 31/7, and today is the 24/7 - the % complete would be 53%. - (I want to have this as a separate column so I can keep dependencies on the actual % complete column)
=(MIN(TODAY(),*finish date*)-*start date*+1)/*finish date*-*start date*+1)
The formula above would work in Excel, but is there a smartsheet alternative? Any help appreciated.
Comments
-
Ok, I've found a new problem -
This formula works if the task has started/ currently being done :
=(ROUND((MIN(TODAY(), FINISH1) - START1 + 1) / SUM((FINISH1 - START1 + 1) / 100)))
The formula below works for tasks not yet started, would show "Not Started", and blank if the number was above 0.
=IF((ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))) < 0, "Not Started","")
How do I write a formula to show a number (which would be the possible % complete) when the value is false, and "Not Started" when the value is true.
-
In your second formula, if you replace the last `""` with the function you want to calculate, it will return a number instead of a blank value.
=IF((ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))) < 0, "Not Started", (ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))))
-
=IF((ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))) <= 0, "Not Started",(ROUND((MIN(TODAY(), FINISH2) - START2 + 1) / SUM((FINISH2 - START2 + 1) / 100))))
Give this a try. If the value is <= 0, it will say "Not Started". Otherwise it will input the value.
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!
-
I ended up using this formula, which I like because it requires Start/End dates to be set, and rather than saying "Not Started" I opted for 0% completion.
I feel this logic is simpler and cleaner as well. I'm sure it could be even better, but I'm no developer.
=IF(OR(ISBLANK(Start2), ISBLANK(Finish2)), "Add Dates", IF(TODAY() > Finish2, 1, IF(TODAY() < Start2, 0, ((TODAY() - Start2) / (Finish2 - Start2)))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 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
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!