Project 101: Testing for NA in a Date field

It is common to have formulas in custom fields in Project 2002 or 2003 (Pro or Standard) that tests the value of a date field. Often this is testing the value of a Baseline date field for the display of a graphical indicator for a task being behind or ahead of schedule. The annoying part is that unless you properly test for the absence of a date you get a bothersome "#Error#" displayed in the field.

You can however test for the NA in a date field like this:

[Baseline Finish]=ProjDateValue("NA")

In context this would be used in the following formula used to drive a stop-light chart type graphical indicator:

IIf([Baseline Finish]=ProjDateValue("NA"),"No baseline",IIf(ProjDateDiff([Baseline Finish],[Finish])/480>=5,"Late by more than 5 days",IIf(ProjDateDiff([Baseline Finish],[Finish])/480>0,"Late","On schedule")))

Without the test for the NA date a task with no baseline saved would display the #Error# message.