Calculation formula for pecentage of task complete by start date and end date

Carolyn Frey

Novice user - I have found the formula below. is "Start Date Actual" the name of the column and is "row" something I need to change? Using 3 columns; Start Date, End Date and % Complete, what changes do I need to make on the formula below? I am also getting "Unparseable" and do not understand what to change. Thank you so much.

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

Martha Hemingway

As a project manager, trying to automate % complete solely around dates is very tricky. I will rely on the individuals that are assigned the tasks to dictate their % complete once they have started their task. It can also make for a complicated and potentially inaccurate picture when basing % complete off of dates. What I've learned to leverage is having a start & end date, a % complete column, a status column (single select drop down type not restricted), and a risk column (checkbox property type). For both Status and Risk, I have locked w/ the below column formulas. That way I'm only adjusting dates & % complete and I can filter reports or add conditional formatting based on status & risk.

Status formula: =IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row < 1, "In Progress", "Complete"))

Risk formula: =IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Complete")), 1, 0)

Hope that makes sense!!

Ryan Sides

Hi Carolyn, yes, you'll need to change the column names in the formula to match your actual column names. if your column names have spaces or numbers, you'll need to wrap them in [ ] in the formula. Like...

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