MS Project - Column with formula does not calculate correctly

Question

I am trying to add an indicator light to my MS Project sheet similar to this one: Late Indicator Tool. I'm using a simplified formula: IIf([% Complete]<>100,DateDiff("d",[Deadline],[Finish]))

For any row that I enter all the information by hand, the formula works perfectly. However, the formula returns 0 for any rows where I paste data in from other project files (even if all I paste in is the task name).

Even if I attempt to use an even simpler formula ([Deadline]-[Finish]), it still returns 0 (and breaks even further by returning 4294925695.29 or 4294925708.67 instead of #Error in the rows where the Deadline is NA).

Has anyone else had any issues with calculated columns in MS Project and can help me fix it?

EDIT: I gave up on this approach when I discovered a work-around: There is a column called "Finish Variance" that will automatically calculate the difference between the Finish date and the date in the "Baseline Finish" column (which I am now using instead of "Deadline").

Answer 1

Your first problem sounds like your project may be corrupted (or the file that you are pasting from). I suggest building a small sample project to see if you can replicate this error. (I could not replicate it.)

As for the second problem, when the Deadline is NA, Project is substituting a default value of the largest unsigned 32-bit integer (2^32-1). To avoid this unintended value, use an If statement in your formula to return your own value in case Deadline is NA.