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

Cathy Wagner

Paul Newcome

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.

Paul Newcome

What would your logic be behind the planned % complete?

Cathy Wagner

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?

Thanks for your help!

Paul Newcome

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

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

We start with

(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%).

Cathy Wagner

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?

Paul Newcome

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.

Cathy Wagner

That works! Thank you, Paul.

Paul Newcome

Happy to help. 👍️

Cathy Wagner

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

Paul Newcome

Try adding something like this...

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

Melissa Torrez

@Paul Newcome

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