Issue Rolling from 2019 to 2020 with Duration Function Formula to Determine Overall Status

We are using the following formula in a project sheet:


Example Schedule Activity Setup:

Task Name - Start Date - Finish Date - %Complete - Status - Duration - Calc

In the Calc column, is the above formula. Line# is the line you are on in the sheet.

The Start and Finish gives your Duration.

You manually enter %Complete based on where you think you are on the activity. 20%, 35% etc. and it rollup up in the summary project line

The Status automatically populates with either Slipping, On Schedule, Complete, Future Task etc. based on the following formula:

=IF([% Complete]3 = 1, "COMPLETE", IF(Finish3 = "", "NO DUE DATE", IF(Finish3 < TODAY(), "LATE", IF([% Complete]3 < [Calc %]3, "SLIPPING", IF(Start3 > TODAY(), "FUTURE TASK", "ON SCHEDULE")))))

We have a summary project line that is showing the following data based on the above:

Task Name: Merchant Lending

Start Date: 10/14/2019

Finish Date: 4/6/2020

% Complete: 63%

Status: Slipping

Duration: 120d

Calc: 99%

Based on the total number of days and the days remaining between start and finish the Calc is showing we should be at 99% complete which seems incorrect and pushes us to a Slipping status. We do not have this issue with activities in the current year only those rolling from 2019 to 2020.

What could be the issue and resolution? Thank you!


  • Hi Stacey,


    Happy to help, from the description provided it sounds like the % Complete column is inaccurately producing a value of 99% in a Parent row (summary row of child rows). If this is the case it is likely occurring because a child row doesn't contain a Start or End Date. For the % Complete to accurately reflect in the Parent row all child rows must contain a Start / End Date and the desired percentage. When these dates in the child rows are not present these rows are not included in the roll-up. 


    If the issue continues after performing the above I would suggest asking our tech support team for assistance that way we can further investigate. Be sure to include screenshots of your sheet.


    Have a wonderful day. Thank you for contacting Smartsheet Support.




    Smartsheet Technical Support

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!