Formula help: IF AND (drop down list in column and date column)

April Rosier

I need help with a formula.

 

I have 2 columns I need to test true for. I would like that column to indicate Red, Yellow, or Green in the "Health Column".

 

I need the "SCAA Stage 1 Completed Date" column and the "Content" columns to work together. One is a list and one is a date.

 

I would like logically if SCAA column is a date AND Content Column contains ("InfoSec", "InfoSec (Custom PCI)", or "Custom") THEN = GREEN.

 

If one of these is not true then YELLOW (meaning SCAA is not a date OR Content is any other value).

 

If both are NOT TRUE then = RED.

 

I have this working:

=IF([SCAA Stage 1 Completed Date]11 = "⌛", "Yellow", IF(Content11 = "L.PCI / D.PII", "Yellow", IF(Content11 = "Custom", "Yellow", IF(Content11 = "L.PCI / L.PII", "Yellow", IF(Content11 = "L.PCI", "Yellow", "Green")))))

 

Please help! 

Kennedy Stomps

Hi April-- This might work:

 

=IF(AND(ISDATE([SCAA Stage 1 Completed Date]1), OR(Content1 = "InfoSec", Content1 = "InfoSec (Custom PCI)", Content1 = "Custom")), "Green", IF(AND(ISBLANK(Content1), ISBLANK([SCAA Stage 1 Completed Date]1)), "Red", "Yellow"))

 

If not, let me know and we can troubleshoot this further!