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.

Monday, December 23, 2013 6:58 AM

• 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 Monday, December 23, 2013 2:38 PM
Monday, December 23, 2013 10:06 AM

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"))))

Andrew

Monday, December 23, 2013 8:42 AM
• Hi Andrew,

Thanks for your response, i tried with your suggestion, if the start variance is > 0 , it is always returning"Late"

Monday, December 23, 2013 8:55 AM

IIf([Baseline Finish]=ProjDateValue("NA"),"No baseline",IIf([Start Variance]=0,"On Time",IIf([Start Variance]>=1,IIf([Start Variance]<=5,"Delay","Late"))))

Monday, December 23, 2013 9:14 AM
• Hi Andrew,

Thanks for your help, but still same issue.

Monday, December 23, 2013 9:44 AM
• 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 Monday, December 23, 2013 2:38 PM
Monday, December 23, 2013 10:06 AM
• Hi Andrew,

Thanks a lot for your help. It is working fine.