Formula: percentage based on dates
Answers
-
I'd like to pop this to the top of the queue again.
The formula that I am using is
=IFERROR(IF(ISBLANK(Start@row), "0", 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, "")))))), "")
This works well in that the percentage does change based upon the relationship between the start date and today's date. However, what puzzles me is that, if I were to extend out the end date for the task, I would expect the percentage complete to decrease. However, in this formulation, the longer the task duration, the MORE complete this formula shows the task to be in the % complete column.
For instance: a 3 day task, one day in, shows 42% complete. Extend the end date to make that task 11 days and I get an 87% completion result.
Been trying the math I know to compensate for this but without luck. Hoping someone with greater skills can advise.
-
@Todd Smelser Try this...
=IFERROR(MAX(MIN(1, (TODAY() - Start@row) / (Finish@row - Start@row)), 0), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!