# Create a column that indicates # complete a task should be per the start and finish dates.

Options
✭✭✭✭

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 0-100% depending on when the start and finish dates fall in comparison to current day. O only need 0-100%.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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)), "")

• ✭✭✭✭✭✭
Options

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.

«1

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
Options

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%

• ✭✭✭✭
Options

Could we take this formula and clip it for 0-100?

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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 wrap it in the iferror.

Max(min(networkdays(.....)/duration@row, 0), 1)

If(......., max(min(..........))

Iferror(...............)), "")

• ✭✭✭✭
Options

=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

• ✭✭✭✭✭✭
Options

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)), "")

• ✭✭✭✭
Options

=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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

Boom!! That worked!! Thank you for all the help!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!