Automatic Target %Completion by Date
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!
Comments
-
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)))
Help Article Resources
Categories
Check out the Formula Handbook template!