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?
<= 14part (-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).