Create a column that indicates # complete a task should be per the start and finish dates.
I want to create a column that indicates # complete a task should be per the start and finish dates. This will allow the user to compare expected vs actual % complete.
I have a helper column already that does this for the Risk Flag formula, but it can actually go below or above 0100% depending on when the start and finish dates fall in comparison to current day. O only need 0100%.
Best Answers

For projected % complete, you would use something like this:
=MAX(MIN((TODAY()  [Target Start Date]@row) / ([Target Finish Date]@row  [Target Start Date]@row), 1), 0)

You have one too many closing parenthesis after the today function, two too many after the duration@row, and it look like you have the end mixed up a little bit.
Duration@row, 0), 1)), "")

Sorry. I'm on my phone right now and didn't realize I got the end mixed up in my last post. Switch the one and zero at the end.
Answers

Are you able to provide some screenshots for example? You will need both a MIN and a MAX function to get it to stay between 0 and 100 percent.


For projected % complete, you would use something like this:
=MAX(MIN((TODAY()  [Target Start Date]@row) / ([Target Finish Date]@row  [Target Start Date]@row), 1), 0)

This worked Great!!
There is s slight difference in the calculation and the one we are using to calculate the Risk flag. It seems your formula includes today and the other doesn't include today until the day ends. So it shows 92% while yours shows 96%

Could we take this formula and clip it for 0100?

TO contain a number output to between 0 and 100 percent, you would use the same MIN/MAX combo that I used above.
=MAX(MIN(existing formula, 1), 0)

That is the only formula on the sheet I can't open. Have no idea why. I am the sheet owner.

It looks like it is probably set as a column formula. Right click on any cell within that column to open up the cell menu, then click on "Edit Column Formula" at the bottom.
I personally like to convert to cell formula (right above it) first and make my edits that way. One tiny little mistake in syntax when trying to edit a column formula can become a hassle.
Once you get the formula working as expected as a cell formula, you can then convert it back into a column formula.

Sorry yes I missed that.
Ok, I updated the formula and now all values are at 100% if dates are blank.
If start date is in the future it works and shows 0%.
If Finish date is in the past it works and shows 100%.
Here is formula:
=MAX(MIN(IFERROR(IF([Task not Required for Project]@row <> 1, (NETWORKDAYS([Target Start Date]@row, TODAY())) / Duration@row), ""), 1), 0)

Try moving the max/min to only be around the number output portion.
It will be easiest to do this in three parts.
Max/min around the networkdays/duration portion.
Then add your if.
Then wrap it in the iferror.
Max(min(networkdays(.....)/duration@row, 0), 1)
If(......., max(min(..........))
Iferror(...............)), "")

=IFERROR(IF([Task not Required for Project]@row <> 1, MAX(MIN(NETWORKDAYS([Target Start Date]@row, TODAY())) / Duration@row)), ""), 1), 0))
I probably messed this up.
Getting #Unparseable error

You have one too many closing parenthesis after the today function, two too many after the duration@row, and it look like you have the end mixed up a little bit.
Duration@row, 0), 1)), "")

=IFERROR(IF([Task not Required for Project]@row <> 1, MAX(MIN(NETWORKDAYS([Target Start Date]@row, TODAY()) / Duration@row, 0), 1)), "")
As long as there is at least one day duration, it stays at 100% now all the time no matter what.

Sorry. I'm on my phone right now and didn't realize I got the end mixed up in my last post. Switch the one and zero at the end.

Boom!! That worked!! Thank you for all the help!!
Help Article Resources
Categories
Check out the Formula Handbook template!