Baseline Variance problem 2

[Planner18]

Hello,

I have been trying to use a formula from a previous post to measure the number of days between finish and baseline finish. The formula I have been trying to use is:

IIf(IsDate([Finish])=No,"No Finish",IIf([Baseline Finish]=ProjDateValue("NA"),"No Baseline Finish",int([Finish]-[Baseline Finish])))

The formula seems to work to a point. However, if the schedule has not been baselined then a '#ERROR' occurs.

Does anyone know how to amend the formula so that it returns 'No Baseline Finish' rather than #ERROR statement?

Your help would be much appreciated

Best regards

Planner

 

[johnherman]

You might get better help in the VB Script forum, or some other language forum.
VBScript Forum forum329


==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[SkipVought]

...or forum707

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Planner18]

Thanks John for your response.

From previous posts, Julie is excellent at coming up with a solution to solve these little puzzles rather than going through the VB script route. However, if this is not possible and I try the approach you have suggested.

Cheers

And thanks Skip for additional info

 

[johnherman]

Thanks Skip! I wasn't aware of that forum!

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[SkipVought]

Wow, John, since 2003?

There hasn't been much Project traffic, but you'll get general VBA help and perhaps a better understanding of how object models work. Look for you there!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JulieInMaine]

Are you entering the formula in a text field? The IIf([Baseline Finish]=ProjDateValue("NA"),"No Baseline Finish" argument should return "No Baseline Finish" if the schedule has not been baselined.

Is there a reason why you aren't using the Finish Variance field? It calculates the difference between Finish and Baseline Finish.

I've just tested the formula and for tasks without a baseline it returns "No Baseline Finish".

 

[Planner18]

Hi Julie,

The reason is because PM's don't always have baseline dates in their table and when they include tasks following an agreed change they forget to baseline those tasks. All that finish variance shows is 0 days and does not give an indication that tasks have not been baselined which is why I wanted the formula.

The problem seems to have gone and is working fine now, although I don't why.

Thanks for your help.

 

[JulieInMaine]

Okay. Great to hear you have it sorted. If the issue is if a baseline is saved or not - I would just simply the formula and add it to a Flag field to be able to test whether a baseline is saved.

For example in a Flag field use the formula: [Baseline Finish]=ProjDateValue("NA"). The field will return No for tasks with a baseline and Yes for tasks missing a baseline. You can change the formula results to graphic indicators and/or use filters to see those tasks without a baseline.

 

[Planner18]

Excellent idea as always Julie. Many thanks

 

[JulieInMaine]

You're welcome and thank you for the feedback.

 

[Planner18]

Hi Julie,

Having another problem with the formula. When I insert additional activities and task after baselining, the summary rows show an entry, even though I have set the 'calculation for task and group summary' and 'calculation for assignment rows' set to none, and I would like all summary rows to be blank. Is there something standard that you can put in any formula that would ignore summary rows and leave them blank?

Many thanks

 

[JulieInMaine]

I'm not sure what formula you are using now - but [Summary] = "Yes", " " or something along those line should work. If you have left the calculations to none, however, the field should be empty.

 

[Planner18]

Hi Julie,

I'm using the following formula:

IIf(IsDate([Finish])=No,"No Finish",IIf([Baseline Finish]=ProjDateValue("NA"),"No Baseline Finish",int([Finish]-[Baseline Finish])))

I tried introducing [Summary] = "Yes", " " in to this formula but keep getting errors. Can you show me please the right way

Cheers

 

[JulieInMaine]

Try:

IIf([Summary] = yes, "", IIF(IsDate([Finish])=No,"No Finish",IIf([Baseline Finish]=ProjDateValue("NA"),"No Baseline Finish",int([Finish]-[Baseline Finish]))))

Again - if the "Calculation for task and group summary rows" is set to None, the formula shouldn't need alteration. You might try using the original formula in another spare text field.

 

[Planner18]

Julie, Many thanks once again.

I did try that but it didn't seem to work. Maybe I mistyped it somehow. I will try again.

It is strange I would have expected the same outcome setting "Calculation for task and group summary rows" to None. I'll will try your suggestion of using another text field and let you know.

Cheers

 

[JulieInMaine]

Keep us posted.

 

[Planner18]

Julie,

I used the formula in another text field and set the 'Calculation for task and group summary rows" to 'None' and the same problem occurred when new tasks were added. Then switched to 'use formula' and the output against summary tasks disappeared.

Therefore, the formula gives me what I need. However, it is strange why the 'none' option doesn't have the same result.

Thank you for your help.

Regards

Planner18

 

[JulieInMaine]

You're welcome and glad to know it's sorted.