Date Compliance Check - Microsoft Project Formula

Here is a quick little formula that I developed that can help you keep an eye on your project's status.  I have some general rules for my project plans:

1) No manually typed dates
2) All tasks should have a predecessor
3) No past due start dates
4) No past due finish dates

To help with numbers 3 and 4, I have written the following formula:

IIf([% Complete]<100,Switch(([% Complete]=0 And (DateValue([Current Date])-DateValue([Start]))>0),1,((DateValue([Current Date])-DateValue([Finish]))<-5),3,(DateValue([Current Date])-DateValue([Finish]))>=-5 And (DateValue([Current Date])-DateValue([Finish]))<0,2,(DateValue([Current Date])-DateValue([Finish]))>=0,1),3)

If you open up Microsoft Project, insert a number field, and then right click the field and choose "Customize Fields," you will be able to select the "Formula" button and paste the formula in.  After you accept the entry, you can setup "Graphical Indicators" to show a Red "X" for the result of a 1, and a Green Flag for the result of a 2.

The results are as follows:

1 - The task has a past due start date and the task has not started or has a past due finish date and the % complete is not marked at 100.
2 - This task will be completing within the next 5 days.
3 - Does not meet conditions 1 or 2.

Anything with a red "X" should be dealt with.  Did the task start?  If so, then update the % complete.  If it did not start, then move the date to the anticipated start date.  If the task is in progress then the finish date may have been missed.  Has the task finished?  If so, mark the task 100% complete, if not, then move the finish date to the anticipated finish date.

Hope this helps!

Rick