IF(AND Statement help please

Heath Sanders

Hi Team,

The top formula is my "12 Month Warranty Period" that I copied from my "90 Day Defects Period" (Below) They both do the same thing, when I choose "Completed" in my drop box the 12 Month column kicks in.  90 Day works well, I'm having trouble with the 12 Month, its doing random stuff. 

6 months it should say  "Red", 180 days (6 months) to 30 days (1 month) should be "Yellow" ,  30 days to 1 day "Green" , on the day or over is "Over" (Warranty is Over) .     

=IF([PRODUCTION Job Status]8 <> "COMPLETED", "", IF([12 Month Warranty]8 + 365 - TODAY() > 180, "Red", IF(AND([12 Month Warranty]8 + 365 - TODAY() < 180, [12 Month Warranty]8 + 365 - TODAY() > 30), "Yellow", IF(AND([12 Month Warranty]8 + 365 - TODAY() < 30, [12 Month Warranty]8 + 365 - TODAY() > 1), "Green", IF([12 Month Warranty]8 + 365 - TODAY() >= 0, "OVER", IF(ISBLANK([12 Month Warranty]8), ""))))))

=IF([PRODUCTION Job Status]8 <> "DEFECTS 90", "", IF([90 Day Defects Period]8 + 90 - TODAY() > 45, "Red", IF(AND([90 Day Defects Period]8 + 90 - TODAY() < 45, [90 Day Defects Period]8 + 90 - TODAY() > 8), "Yellow", IF(AND([90 Day Defects Period]8 + 90 - TODAY() < 9, [90 Day Defects Period]8 + 90 - TODAY() > 0), "Green", IF(ISBLANK([90 Day Defects Period]8), "", "90 Day Defects Over")))))

 

Robert S.

Hello Heath,

 

Thanks for the question. If I understand how you'd like this to function, I believe writing the formula like this should work:

 

=IF(OR(ISBLANK([12 Month Warranty]8), [PRODUCTION Job Status]8 <> "COMPLETED"), "", IF([12 Month Warranty]8 + 365 - TODAY() >= 180, "Red", IF(AND([12 Month Warranty]8 + 365 - TODAY() < 180, [12 Month Warranty]8 + 365 - TODAY() >= 30), "Yellow", IF(AND([12 Month Warranty]8 + 365 - TODAY() < 30, [12 Month Warranty]8 + 365 - TODAY() >= 1), "Green", "OVER"))))

 

This will set the cell to...

• BLANK if either PRODUCTION Job Status is not "COMPLETED" or 12 Month Warranty is blank
• "Red" if greater than or equal to 180
• "Yellow" if less than 180 and greater than or equal to 30
• "Green" if less than 30 and greater than or equal to 1 
• "OVER" if anything else

 

I also noticed that the 90 Day formula you provided will show what seems to be unintended results in some circumstances. For instance if it's exactly 45, then it results in "90 Days Defects Over" which I don't believe is correct. If I understand correctly, this is how the formula could be written to resolve that.

 

=IF(OR(ISBLANK([90 Day Defects Period]10), [PRODUCTION Job Status]10 <> "DEFECTS 90"), "", IF([90 Day Defects Period]10 + 90 - TODAY() >= 45, "Red", IF(AND([90 Day Defects Period]10 + 90 - TODAY() < 45, [90 Day Defects Period]10 + 90 - TODAY() >= 8), "Yellow", IF(AND([90 Day Defects Period]10 + 90 - TODAY() < 8, [90 Day Defects Period]10 + 90 - TODAY() >= 1), "Green", "90 Day Defects Over"))))