Issue Rolling from 2019 to 2020 with Duration Function Formula to Determine Overall Status
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!
Answers
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!