Help! trying to figure out a formula for dates!

Kelly Eccles

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!!!

Paul Newcome

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)

Kelly Eccles

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)

Kelly Eccles

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!!!!

Paul Newcome

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)

Kelly Eccles

This is awesome - thank you!!!

Paul Newcome

Happy to help. 👍️