1

I've been struggling with a Microsoft Project (2013 Standard) formula assigned to a number user-defined field to calculate the difference between tasks' current Finishes and a baseline Finish I've previously set. 

Switch(
[Finish]="",0,
[Baseline1 Finish]="",0,
[Finish]=projdatevalue('NA'),0,
[Baseline1 Finish]=projdatevalue('NA'),0,
[Finish]-[Baseline1 Finish]<=0,0,
[Finish]-[Baseline1 Finish]>0,[Finish]-[Baseline1 Finish]
)

On tasks for which [Baseline1 Finish] is "NA" I consistently get a #ERROR, even though I should have trapped for it between lines 3 & 5 above. I've tried everything I can think of, such as:

  • Using an equivalent IIF formula
  • Using different user-defined date fields (e.g., [Baseline2 Finish])
  • Removing different elements of the formula one at a time
  • Checking against a "high" date value (e.g., 2^30, since this is how I understand Project actually renders "NA" behind the scenes) instead of using ProjDateValue (c.f. http://masamiki.com/project/customfieldFAQ.htm)

But no seeming change in behavior. Is there a defect in Project when using ProjDateValue in a formula against one of the user-defined date fields?

Improve this question

3 Answers 3

Reset to default
2

Here are a few things to note:

  • Instead of checking the task Finish field for N/A or 0, use the IsDate function.
  • While N/A values for date fields are internally stored as very large integers (2^32) you only need to test against something greater than Project's maximum date (which appears to be #12/31/2099# for Project 2013; #12/31/2049# for earlier versions). Testing against a large integer (e.g. 2^30) might lead to an overflow error.
  • Use the ProjDateDiff function to calculate the difference between two dates. If you don't supply the third parameter (calendar) the project calender is used (which is almost always what you want).
  • Use a Duration field to hold the results of this formula.

This updated formula should work for you:

IIf([Baseline1 Finish]>75000 Or Not IsDate([Finish]),0,ProjDateDiff([Finish],[Baseline1 Finish]))

If desired, you can modify it to force negative deltas to be zero (as you had in your original formula).

Improve this answer
10
  • Thanks for the quick reply @Rachel. When you said "The task Finish field is always a date, so you don't have to check it for N/A or 0" it seems contrary to advice I've seen on multiple web sites to overcome this exact type of issue (c.f. social.technet.microsoft.com/Forums/projectserver/en-US/…).
    – JimMSDN
    Aug 4, 2015 at 21:26
  • As for the question around overflow when comparing - I was concerned about that as well, but that was just in one of techniques I tried and it did not seem to help nor hurt.
    – JimMSDN
    Aug 4, 2015 at 21:31
  • I was using a number user-defined field for the result (which except for the #ERROR cases, produced the correct number) - so I tried a duration user-defined field. It did not change the #ERROR behavior (although it didn't produce the correct numeric result).
    – JimMSDN
    Aug 4, 2015 at 21:32
  • I did try your formula which works (and works "better" since it calculates based upon project days, not days of the week) for the same items - but there are Finish dates that I've actually deleted in the plan (at least from the UI - no idea what Project does under the scenes) and those are now throwing errors. You've given me some more ideas to try - I'll give those a go and report back on what I find.
    – JimMSDN
    Aug 4, 2015 at 21:37
  • 1
    Just a quick comment. With the introduction in Project 2010 of manually scheduled tasks - you are better looking at the Scheduled Finish or Scheduled Start for tasks. They always have a date - even if it is a manually scheduled tasks with text typed in the Start or Finish field.
    – JulieS
    Aug 9, 2015 at 16:06
1

@JimMSDN -- This is an old thread but I would like to add for the future readers: it works differently in formulas. So in MS Project 2010 and later, the Finish field can be blank. And the Baseline Finish can be blank as well. There is a new field Baseline Estimated Finish. But you must first decide whether you want formula to work for both manually scheduled and automatically scheduled tasks. Please see Working with Manually Scheduled Tasks and Errors in Formulas. Regards -- Ismet

Improve this answer
0

There is built in Field in MS project for that Finish variance, which will give you variance between, baseline and current finish date.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.