Automatic Target %Completion by Date

edited 12/09/19 in Formulas and Functions


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: 


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! 



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!