1

I am trying to calculate a Project Health field with the formula below.

IF(
  AND( 
    End_Date__c - TODAY() <= 14, 
    Percent_Complete__c < 75
  ), 
  "At Risk", 
  IF(
    AND( 
      End_Date__c  - TODAY()<= 0, 
      ISPICKVAL(Project_Status__c, "In Progress")
    ) , 
    "Past Due", 
    "Good"
  )
)

It is supposed to show "At Risk" if the end date is less than two weeks away & if the percent complete field is less than 75%.

It is supposed to show "Past Due" if the end date is in the past, & the status is "In Progress".

For anything else it should show "Good".

When testing, no matter what the percent complete is, if it's within two weeks it will always show "At Risk", it's like it's not looking past the first check. & if the end date is in the past, it won't change to "Past Due". Am I using the IF AND setup incorrectly?

Improve this question
1
  • This isn't necessarily the problem (I think sfdcfox hit the nail on the head), but I get the feeling that you should be checking for "past due" first. An end date in the past satisfies the <= 14 part (-1 is less than or equal to 14), so if that record would have a completion percentage > 0.75 it would show as "at risk" instead of "past due" (like it sounds like you're looking for). Since if/else if is "first thing to match wins", criteria should be ordered from most specific to least specific (i.e. most general).
    – Derek F
    Commented Nov 16, 2022 at 17:25

1 Answer 1

Reset to default
0

Percent fields are expressed as a decimal (percent means "per 100 parts"). For example, if you pay 5% tax, you'd calculate it as Amount * 1.05; Salesforce stores the percent as a decimal. You just need to change 75 to 0.75.

Improve this answer
2
  • Of course it was something simple like that. Made the change & it works perfect, cheers!
    – samgiese
    Commented Nov 16, 2022 at 17:55
  • @samgiese Yeah, we all make those mistakes from time to time :D
    – sfdcfox ♦
    Commented Nov 16, 2022 at 17:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .