Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Auto populate Percentage Complete




I have a "task" in my sheet which lasts for 3 months.  Is there a way to autopopulate the percentage complete based purely on the time passed?


It is not a measure of outputs / delivery but just showing how long an external resource is in place.  For example, after 1 month, it would say 30% complete.


Many thanks, Siobhan



  • Brett Evans
    Brett Evans ✭✭✭✭✭✭


    =Today()-StartDate  will give you the number of days since the start.

    =EndDate-Start Date will give you the number of days total for the task

    =(Today()-StartDate)/(EndDate-StartDate)  will give you the percentage complete.



    hope this helps,



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    If your sheet has project Dependencies enabled, the % Complete column can not have a formula in it. This is assuming that the % Complete column (by name) has been chosen as the % Complete column (by function) under the Dependency Settings | Options section. 

    If you turn this 'off' by selecting --None-- in the dropdown, the summary rows will not auto-calculate the rollup percentage.




    The formula provided by Brett will work in another Text/Number column or in the % Complete (by name) column if it is not connected to the Dependency functionality.




  • gabsy1972

    hello Brett,


    thanks for your helpful advise i have used your formula for Percentage complete, however if its a date in the future i get a percentage of  -606% help... sad

    Orders Placed    10d    29/06/18    12/07/18    Green    -606%

  • JenValdez


    I was able to add this formula but for tasks that have already passed, it gives an output of more than 100%. Conversely, for tasks that have not yet reached the start date, it gives a negative percentage. How would I adapt the formula to safeguard against that?


  • Becky Swanwick

    I would like to know the answer to this too. So far, I've worked around the negative percentage appearing before the start date by using conditional formatting to change the font colour in the relevant cell to the same colour as the cell itself to render it invisible. However, I don't want to do this with completed tasks, I simply want them to keep showing 100%. How do I do this?

  • Katie2018

    Nested IF statements work for this: 

    =IF([Start Date]24 > TODAY(), "0%", IF([Due Date]24 < TODAY(), "100%", NETWORKDAYS([Start Date]24, TODAY()) / Duration24))

    The above formula will return "0%" if the start date is in the future and "100%" if the finish date is in the past.  Note I wrote this to leverage the duration column when dependencies are enabled.  If dependencies are not enabled, you will need to follow the original formula posted above.  Also, NETWORKDAYS will take into account any non-working days to adjust your overall progress accordingly.

  • Todd Smelser


    I was able to use your formula (After some customization to my Gantt) and wanted to say thank you!

This discussion has been closed.