Formula: percentage based on dates

System

This discussion was created from comments split from: Am I able to create a formula that will advance the percentage complete based on start and End Date?.

Todd Smelser

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.

Paul Newcome

@Todd Smelser Try this...

=IFERROR(MAX(MIN(1, (TODAY() - Start@row) / (Finish@row - Start@row)), 0), "")