If you are trying to indicate upcoming starts, upcoming finishes, and lateness, then here is a formula to get you started:
IIf([Start]-[Current Date]>=5,"Green",IIf([Start]-[Current Date]>=0,"Yellow",IIf([% Complete]>0,"Blue","Red")))
Insert this formula into a custom text field, and it will display various outputs under the following conditions:
- "Green" if the current date is greater than or equal to 5 days ahead of the start date.
- "Yellow" if the current date is less than 5 days and greater than or equal to 0 days ahead of the start date.
- "Blue" if the current date is past the start date and the task % complete is greater than 0 (i.e. the task has started).
- "Red" if the current date is past the start date and the task % complete is 0 (i.e. the task has not started).
You can use similar logic to create another custom calculated field based on the finish date.
