Answered by:
Project Server Custom Field Formula
Question

Hi All,
I am looking for a custom filed formula using start variance. The requirement is
When there is no baseline output should be "No baseline"
When start variance =0 output should be "On Time"
When start variance >= 1 and <=5 output should be "Delay"
When Start variance > 5 output should be "Late"
I tried with the below
IIf([Baseline Finish]=ProjDateValue("NA"),"No baseline",IIf([Start Variance]=0,"On Time",IIf(([Start Variance]>=1)
And ([Start Variance]<=5),"Delay","Late")))Every thing working fine but when the start variance is between 1 to 5 it is not returning "Delay" as expected. i.e when the start variance > 0 it is always returning "Late". Whether something wrong with the formula.
Thanks in advance, Arun
Answers

Ah  the Start Variance value is stored as minutes rather than days, so the Start Variance is greater than 5 minutes, so shown as late.
Change the 1 and 5 values to multiply by the number of hours per day * 60, assuming 8 hours per day, 1 would become 480, and 5 would become 2,400.
Also, your logic doesn't appear to account for values between 1 and 0 days, so try this:
IIf([Baseline Finish]=ProjDateValue("NA"),"No baseline",IIf([Start Variance]<480,"On Time",IIf([Start Variance]>=480,IIf([Start Variance]<=2400,"Delay","Late"))))
 Marked as answer by arun_2014 Monday, December 23, 2013 2:38 PM
All replies

Hello,
I've never used "And" within an IIF statement. I think you just need to embed another if statement to get the AND logic:
IIf([Baseline Finish]=ProjDateValue("NA"),"No baseline",IIf([Start Variance]=0,"On Time",IIf([Start Variance]<=5,"Delay",IIf([Start Variance]>=1,"Late"))))
I hope that's helpful,
Andrew




Ah  the Start Variance value is stored as minutes rather than days, so the Start Variance is greater than 5 minutes, so shown as late.
Change the 1 and 5 values to multiply by the number of hours per day * 60, assuming 8 hours per day, 1 would become 480, and 5 would become 2,400.
Also, your logic doesn't appear to account for values between 1 and 0 days, so try this:
IIf([Baseline Finish]=ProjDateValue("NA"),"No baseline",IIf([Start Variance]<480,"On Time",IIf([Start Variance]>=480,IIf([Start Variance]<=2400,"Delay","Late"))))
 Marked as answer by arun_2014 Monday, December 23, 2013 2:38 PM
