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.

1 Answer 1



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]

  • 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
    Commented 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
    Commented 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
    Commented 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
    Commented 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
    Commented Oct 4, 2016 at 6:35

Your Answer

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

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