# I'm looking to add a column and formula to calculate Planned % Complete. Can anyone help?

• Try this...

=IF([Start Date]@row <= TODAY(), MIN((TODAY() - [Start Date]@row) / Duration@row, 1), 0)

Basically... If the Start Date is in the past, run the formula, otherwise populate 0.

• What would your logic be behind the planned % complete?

• Hi Paul - I'm thinking that if start date is x and duration is y, then as of today, this task should be xx% complete. I really want to see the planned % complete at the parent (and sub-parent) levels, but I think I may need to calculate planned % complete at the task level and then allow the system to do the roll-up at the parent levels. Most of our tasks have fairly short durations.

Does that make sense?

• So you would want something along the lines of...

=MIN((TODAY() - [Start Date]@row) / Duration@row, 1)

(TODAY() - [Start Date]@row) / Duration@row

to give us the planned % complete. We don't want it to go over 100% though, so we use the MIN function to essentially cap it at 1 (which equals 100%).

• Hi Paul - just getting back to this. Happy New Year! The formula worked for tasks in progress, however, I have a little problem with negative numbers for tasks not yet started. Thoughts?

• Try this...

=IF([Start Date]@row <= TODAY(), MIN((TODAY() - [Start Date]@row) / Duration@row, 1), 0)

Basically... If the Start Date is in the past, run the formula, otherwise populate 0.

• That works! Thank you, Paul.

• Happy to help. 👍️

• Paul - I've run into a little problem. It looks like the formula you provided works great at the task level. I need to be able to calculate a rolled up view at the parent, grandparent, etc. level, which has to take into account each task in the same way that the inherent "% Complete" formula works. Make sense?

Thoughts?

Cathy

• Try adding something like this...

=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), original formula)

• I am doing something similar but I don't think it is working as it should.

=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(Start@row < Today@row, MIN((Today@row - Start@row) / (Finish@row - Start@row), 1), 0))

When I update the Start and Finish to yesterday (06/01/2022) I get an error

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!