Help! trying to figure out a formula for dates!

Options
✭✭✭

Hi! I am trying to create a formula for dates. I have start date and end date which are 70 days apart & i need to have a formula that calculates the % complete, based on the start date and current date....

I have tried and tried to figure this out but i am stuck!

Thank you!!!

• ✭✭✭✭✭✭
Options

You would take the number of days elapsed and divide it by the total number of days.

=(TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)

• ✭✭✭
Options

Thank you! I have been looking at one of your other formulas today! I got an invalid operation....?

=(TODAY() - [Ignite Start Date]@row) / [Ignite Finish Date (10 weeks)]@row - [Ignite Start Date]@row)

• ✭✭✭
edited 10/18/22
Options

DOH! I just saw i missed a bracket! ok that works! How can i then cap it so that if it is over, i can set to 100%?

ps - thank you!!!!

• ✭✭✭✭✭✭
Options

To cap it at 100% you would include a MIN function so that it pulls the lowest value either 100% or the formula's output.

=MIN((TODAY() - [Ignite Start Date]@row) / ([Ignite Finish Date (10 weeks)]@row - [Ignite Start Date]@row), 1)

• ✭✭✭
Options

This is awesome - thank you!!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!