none
Project Server Custom Field Formula RRS feed

  • 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

    Monday, December 23, 2013 6:58 AM

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

    I hope that's helpful,

    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"


    Thanks in advance, Arun

    Monday, December 23, 2013 8:55 AM
  • Apologies, try this instead:

    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.


    Thanks in advance, Arun

    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 arun_2014 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.


    Thanks in advance, Arun

    Monday, December 23, 2013 2:38 PM