0

I've set a formula to monitor variance between baseline and scheduled finish. I know that there is a simple finish variance column for this kind of thing, but in my organization we've got certain deviations thresholds so default field set will not work. Please take a look at this

Switch(Not [Baseline Finish]=[Scheduled Finish];Iif((DateDiff("d";[Baseline Finish];[Scheduled Finish])>14) OR (DateDiff("d";[Baseline Finish];[Scheduled Finish])<-14);DateDiff("d";[Baseline Finish];[Scheduled Finish]);"");([Baseline Finish]=[Scheduled Finish] And [% Complete]=100) Or ([Baseline Finish]=[Scheduled Finish] And [% Complete]=0);"")

What is achieved here is that in a text30 field the variance data is only displayed if it meets certain threshold parameters (e.g. -14 and +14 days). The need for this is that i do have a set of flags that trigger to YES/NO depending on text30 column data (e.g. red arrow if deviation is >60 or green arrow if deviation is >15<31 etc). And then based on those flags the custom milestone icons are displayed on gantt. Question is how can i simplify the a/m formula?

Thank you.

Improve this question

1 Answer 1

Reset to default
0

Try: Switch([Finish Variance]/[Minutes Per Day]<=-14,[Finish Variance]/[Minutes Per Day],[Finish Variance]/[Minutes Per Day]>=14,[Finish Variance]/[Minutes Per Day],[Finish Variance]/[Minutes Per Day]<14 And [Finish Variance]/[Minutes Per Day]>-14," ")

As you are testing for % complete in the flag formulas I'd just show [Finish Variance] in Text30. Because you are using a text field, the Finish Variance is going to be in minutes - hence dividing it by [Minutes per day]

Improve this answer
7
  • Oh i forgot to mention that % Complete check is there to have a text30 field empty when task is marked as %complete=100 even though if there is a schedule variance in place. Basically if the task is complete then forget about the variance and don't show it to anyone. However it is not really working as intended... So probably there is a logical error there somewhere...
    – Calypsys
    Sep 30, 2016 at 6:38
  • Then add one more test, result of ([% complete] = 100, " ") as the first in the series. The formula stops at the first true result.
    – JulieS
    Oct 1, 2016 at 16:10
  • I tried your formula but the result differs. e.g. my formula shows 26days variance between 19.08.16 baseline and 14.09.16 scheduled finish. When i apply yours, it shows 18days instead. It seems to skip holidays. For our internal reporting reasons we reflect delays with holidays included
    – Calypsys
    Oct 3, 2016 at 13:08
  • I'm not sure I follow. Are you saying that you count non-working days (Saturday and Sunday) when calculating variance? Were the resource scheduled to work during Saturday and Sunday?
    – JulieS
    Oct 3, 2016 at 14:11
  • In our case we do not use resource planning at all. It is all based on calendar planning. This is just how it is in mt organization
    – Calypsys
    Oct 4, 2016 at 6:35

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.