What is a formula for % complete to be a percentage based on progress from Start Date to End Date

John Eifler

I am attempting to have my % Complete column represent progress as a function of today in relation to the difference between Start Date and End Date columns.

An example: Today is 11/17/21 Duration is 5d, Start Date is 11/16/21, end date (generated from duration) is 11/22/21, so we are 1 day from the start date (day 2) with a duration of 5 days. 2/5 is .40, or 40%.

The goal is that at the beginning of each day, there should be an increase of the % Complete by 20%. Start Date (day 1)=20%, beginning of day 2=40%...End Date (day 5) =100%.

Continuing with the example, today the % Complete should read 40%, when I open Smartsheet tomorrow, it should read 60%, and on the End Date when I open Smartsheet it should read 100%.

Is there a way to accomplish this with a formula. Thank you, in advance.

Paul Newcome

Duh. I did it backwards. Time for more coffee. We take the current duration and divide it by the total duration. Sorry about that.

=NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row

Paul Newcome

Try this...

=NETWORKDAYS([Start Date]@row, MIN(TODAY(), [End Date]@row)) / Duration@row

Paul Newcome

Here is what I would add...

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

Basically it says that if the start date is in the past output the result of the percentage formula, otherwise output zero.

Paul Newcome

You would divide the total duration by the current duration (which is today minus start date).

=Duration@row / NETWORKDAYS([Start Date]@row, TODAY())

John Eifler

Hi @Paul Newcome !

Thank you for responding. Unfortunately, that formula returns 1.666667, or 167% while I am looking for 60% for today (3 days from start date out of 5d duration) 80% tomorrow, 100% on the 5th day (end date).

For further clarification, the Gantt settings are: Date range display is Start Date and End date, with "None" for Options (in order to write formula in % complete column), Dependencies are enabled using Predecessors column and Duration column. Working days are Mon-Fri, 8hr days, with no Non-Working days.

Thanks again, I hope the clarification is useful.

Paul Newcome

Duh. I did it backwards. Time for more coffee. We take the current duration and divide it by the total duration. Sorry about that.

=NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row

John Eifler

Thank you, @Paul Newcome !

That worked perfectly. One more question, how would I make it to not go over 100%, or how to stop the calculation on the end date but keep 100% in the column. Thanks, again.

Paul Newcome

Try this...

=NETWORKDAYS([Start Date]@row, MIN(TODAY(), [End Date]@row)) / Duration@row

John Eifler

Thanks, @Paul Newcome ! Your help has saved me hours of effort and who knows how much mental anguish.

Paul Newcome

Happy to help. 👍️

John Eifler

You're not going to believe this, @Paul Newcome, but I can't figure out how to have it not return a percentage less that 0 for when the start date is in the future. Would you please teach me how to do that?

Paul Newcome

Here is what I would add...

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

Basically it says that if the start date is in the past output the result of the percentage formula, otherwise output zero.

John Eifler

Thank you, again @Paul Newcome. Worked perfectly. Your help and willingness to share knowledge is appreciated.

Paul Newcome

Happy to help. 👍️

Rangifer

Hi guys, it is not working for me, unfortunately.

It keeps saying #invalid operation after I use this formula. Appreciate any help.

=NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row

Jeff Casto

Hi all,

I receive a similar, "Invalid Data Type" error message. There sure seem to be a number of formulas attempting to provide an automatic %complete calculation. Anyways, thanks for the clues!

Paul Newcome

@Rangifer Make sure the Start Date column is set as a date type column. Are you using dependencies?

@Jeff Casto Are you able to provide a screenshot and the exact formula you are using for reference?

Jeff Casto

Hello @Paul Newcome !

Thank you for the follow up.

Here's a screenshot of the formula in the sheet.

Thank you for all the help!