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

Stacey Grey

We are using the following formula in a project sheet:

(TODAY()-StartLine#)/DURATIONLine#

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!

Eric M Oliveira

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. https://help.smartsheet.com/contact

 

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

 

Cheers,

Eric

Smartsheet Technical Support