Automatic Target %Completion by Date

JenValdez

Hello, 

I'm trying to configure a column to reflect the Target % Completion based on days from Finish date but am hitting a snag for Finish Dates that have passed (resulting in a percentage higher than 100) or Start Dates that haven't arrived yet (resulting in a negative percentage).

(I do understand that you can't add a formula in the %Complete column unless you turn off the dependencies. I decided to create another Text/Number column so I could input a formula). 

The current formula I'm using is: 

=(Today()-Start)/(Finish-Start)

Any suggestions on how I can adapt to prevent +100% or -% would be greatly appreciated! Ideally anything over 100% would just default to 100% and anything negative would just default to 0%.

Thanks in advance! 

ddjerin

The formula is a bit long, but try:

=IF((Today()-Start)/(Finish-Start) >1, 1, IF((Today()-Start)/(Finish-Start) <0, 0, (Today()-Start)/(Finish-Start)))