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!
