# %Complete

Options

I am looking for a formula that will calculate % Complete based on two dates. I have been successful at getting this formula to work on projects that have already began but completed dates or not started dates are giving me a percentage that is negative or above 100. I would like projects that have not started to say "0" and projects completed to show "100". For example

Thank you for anyone who is able to support with this.

• ✭✭✭✭✭✭
Options

Try this...

=IF((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row) > 1, 1, IF((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row) < 0, 0, (TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row))

• ✭✭✭✭✭✭
Options

Take your "original_formula" and drop it into a nested IF statement to say that if it is less than zero then output zero, if it is greater than 1, output 1, otherwise output the "original_formula".

=IF(original_formula < 0, 0, IF(original_formula > 1, 1, original_formula))

• Options

Thank you so much for the feedback. I keep getting an unparsable return. Am I using the () in the wrong spaces?

=IF(((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row)) < 1, ((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row)) <0,0, IF(((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row)) < 1, ((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row))>1,1,(((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row)) < 1, ((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row)))

• ✭✭✭✭✭✭
Options

What exactly is your original formula?

• Options

=IF(((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row)) < 1, ((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row)))

• ✭✭✭✭✭✭
Options

Try this...

=IF((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row) > 1, 1, IF((TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row) < 0, 0, (TODAY() - [Screenshots for Job Aid Due Date]@row) / ([Go Live]@row - [Screenshots for Job Aid Due Date]@row))

• Options

Thank you so very much!! I truly appreciate this!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

Hello,

I am using a similar formula to calculate % complete based on a start and end date. It works unless the start and end date are on the same day. In this case, it returns an error that it cannot divide by zero. Any suggestions?

Formula: =IF((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row) > 1, 1, IF((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row) < 0, 0, (TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)))

Example error:

• ✭✭✭✭✭✭
Options

@Morgan Stokes Try something like this...

=IF(AND([Start Date]@row = [End Date]@row, TODAY() > [Start Date]@row), 1, IF((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row) > 1, 1, IF((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row) < 0, 0, (TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row))))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!