I am looking to automatically populate a "STATUS" column with 1 of 5 values based on critera/conditions are outlined below. The key drivers here are the
- [% Complete]
- [Start] Date
- [F/C Finish] Date - this is a column I have set up that uses a formula to predict a Forecast Finish
([F/C Finish] = (Today() + (R x D)) where R=% Remaining (1-[% Complete]) and D=Duration.
It would be much appreciated if you could take some time to show me how I best can achieve this. Can it be done in 1 formula? If so any guidance would be much appreciated.
Value 1: "Not Due to Start"
[Start] is in the FUTURE AND [%Complete] is equal to or less than 0%
Value 2: "Due to Start" - Task should have started but hasn't!
[Start] is in the PAST AND[%Complete] is equal to or less than 0%
Value 3: "On Track" - Task has started and the F/C Finish date is on time
[Start] is in the PAST AND [F/C Finish] is earlier or the same as the [Finish] date
Value 4: "At Risk" - Task has started and the F/C Finish date is LATE
[Start] is in the PAST AND [F/C Finish] is later than the [Finish]
Value 5: "Complete" -
Task is 100% Complete
Any tips/help much appreciated