IF Cell Equals a value and has a date in other Cell then Green?

[Tony J]

I hope someone will be able to help me

In the attached screenshot you can see that the scope of work (column D) there is a list of transport jobs, if they have the necessary training in columns PMVA, FREC 3, FREC 4, ACA, DCA (Column E to I) which would be a date and it greater than today then I would like the cell in column to eith say Green or show green

I had something like this in mind - but know it would be a lot more complex than this;

=IF(AND(D4="Mental Health",E4>TODAY()),"Green",""))

This would return Green if the date was in a weeks time - but I cannot seem to find a way to add the other rules;

Mental Health needs a PMVA date greater than today to be green
HDU needs a FREC3 and FREC4 date greater than today to be green
PTS needs a FREC3, FREC4 and ACA date greater today to be green
but if PTS has a date in DCA it should be orange
Courier and Staff Transport need all dates to be greater than today to be green

Is this possible or are there too many nested IFs, ANDs, ORs in the formula for it to be possible

This is my first time posting so I hope I have posted correctly and it makes sense

 

[etaf]

,IF(OR(A2="mental Health",AND(A2="HDU",OR(C2> ........

,IF(OR(AND(A2="mental Health",B2>today()),AND(A2="HDU",OR(C2>

 

[Tony J]

,IF(OR(A2="mental Health",AND(A2="HDU",OR(C2> ........

,IF(OR(AND(A2="mental Health",B2>today()),AND(A2="HDU",OR(C2>

Perfect thank you so much

 

[etaf]

the conditional format formula also needs to be change - if this gets your boss approval
=OR(AND($A1="mental Health",$B1>today()),AND($A1="HDU",OR($C2>TODAY(),$D1>TODAY())),AND($A1="PTS - Non Walker",OR($C1>TODAY(),$D1>TODAY(),$E1>TODAY())))

The reason for using row 1 - is I select the entire columns by clicking on the letters
and $ so it references the correct cells and does not change as it moves across and will highlight the row as shown