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]

you can use conditional formatting to actually change the colour

Book3
	A	B	C	D	E	F	G
1	scope of work	PMVA	FREC 3	FREC 4	ACA	DCA	Colour
2	Mental health	12/25/20					green
3	HDU		12/25/20	1/23/21			green
4	PTS		12/25/20	1/23/21	1/1/21		green
5	Staff Transport		12/25/20	1/23/21	1/1/21	1/1/21	green
6	Courier		12/25/20	1/23/21	1/1/21	1/1/21	green
7	PTS					1/1/18	orange
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=IF(AND(A2="PTS",F2<>""),"orange",IF(OR(AND(A2="mental Health",B2>TODAY()),AND(A2="HDU",C2>TODAY(),D2>TODAY()),AND(A2="PTS",C2>TODAY(),D2>TODAY(),E2>TODAY()),AND(A2="Courier",C2>TODAY(),D2>TODAY(),E2>TODAY(),F2>TODAY()),AND(A2="staff transport",C2>TODAY(),D2>TODAY(),E2>TODAY(),F2>TODAY())),"green",""))

 

[Tony J]

Thanks I will give it a go when I get in the office tomorrow - much appreciated

 

[etaf]

you are welcome , I have also added a conditional format to colour the rows
i had trouble adding the green all in 1 formula - not sure why, so spilt out to 2 formulas for green

Book3
	A	B	C	D	E	F	G
1	scope of work	PMVA	FREC 3	FREC 4	ACA	DCA	Colour
2	Mental health	12/25/20					green
3	HDU		12/25/20	1/23/21			green
4	PTS		12/25/20	1/23/21	1/1/21		green
5	Staff Transport		12/25/20	1/23/21	1/1/21	1/1/21	green
6	Courier		12/25/20	1/23/21	1/1/21	1/1/21	green
7	PTS					1/1/18	orange
8
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=IF(AND(A2="PTS",F2<>""),"orange",IF(OR(AND(A2="mental Health",B2>TODAY()),AND(A2="HDU",C2>TODAY(),D2>TODAY()),AND(A2="PTS",C2>TODAY(),D2>TODAY(),E2>TODAY()),AND(A2="Courier",C2>TODAY(),D2>TODAY(),E2>TODAY(),F2>TODAY()),AND(A2="staff transport",C2>TODAY(),D2>TODAY(),E2>TODAY(),F2>TODAY())),"green",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:F	Expression	=AND($A1="PTS",$F1<>"")	text	YES
A:F	Expression	=OR(AND($A1="Courier",$C1>TODAY(),$D1>TODAY(),$E1>TODAY(),$F1>TODAY()),AND($A1="staff transport",$C1>TODAY(),$D1>TODAY(),$E1>TODAY(),$F1>TODAY()))	text	NO
A:F	Expression	=OR(AND($A1="mental Health",$B1>TODAY()),AND($A1="HDU",$C1>TODAY(),$D1>TODAY()),AND($A1="PTS",$C1>TODAY(),$D1>TODAY(),$E1>TODAY()))	text	NO

 

[Tony J]

Brilliant. I wish I had the capabilty to do this. This worked just as I had asked for but when I went back and showed my boss he changed his mind on the rules and said that he wanted it to be green if the date was only one of the training courses for example;

Mental Health only needs PMVA
HDU could be FREC3 or FREC4
Staff Transport could be any
Courier could be any
PTS could be FREC3 or FREC4 or ACA

I thought it would just be as simple as adding the bolded lettering below - but it was not

=IF(AND(A2="PTS",F2<>""),"orange",IF(OR(AND(A2="mental Health",B2>TODAY()),AND(A2="HDU",C2>TODAY(),D2>TODAY()),OR(AND(A2="PTS",C2>TODAY(),D2>TODAY(),E2>TODAY()),OR(AND(A2="Courier",C2>TODAY(),D2>TODAY(),E2>TODAY(),F2>TODAY()),OR(AND(A2="staff transport",C2>TODAY(),D2>TODAY(),E2>TODAY(),F2>TODAY())),"green",""))

Is it possible to have nested / Muliple OR queries?

 

[etaf]

OK
OLD RULES
GREEN
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 AND FREC4 AND ACA date greater today to be green
Courier and Staff Transport need all dates to be greater than today to be green

ORANGE
but if PTS has a date in DCA it should be orange

SO the NEW rules for Orange - NO CHANGE
NEW RULES for GREEN now are
Mental Health only needs PMVA, NO CHANGE
HDU could be FREC3 or FREC4 - THIS is NOW an OR rather than an AND
Staff Transport could be any - nolonger ALL - BUT ANY of the dates - so AN OR
Courier could be any - nolonger ALL - BUT ANY of the dates - so AN OR
PTS could be FREC3 OR FREC4 OR ACA

IS that correct?

OR(AND(A2="PTS",C2>TODAY(),D2>TODAY(),E2>TODAY()),
you need the OR and AND reversed
so the AND is A2 = PTS AND , any of the other columns so an OR

AND(A2="PTS",OR(C2>TODAY(),D2>TODAY(),E2>TODAY()))
Which means A2 MUST have PTS AND anyone of the C2,D2,E2 MUST be > then today

 

[Tony J]

OK
OLD RULES
GREEN
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 AND FREC4 AND ACA date greater today to be green
Courier and Staff Transport need all dates to be greater than today to be green

ORANGE
but if PTS has a date in DCA it should be orange

SO the NEW rules for Orange - NO CHANGE
NEW RULES for GREEN now are
Mental Health only needs PMVA, NO CHANGE
HDU could be FREC3 or FREC4 - THIS is NOW an OR rather than an AND
Staff Transport could be any - nolonger ALL - BUT ANY of the dates - so AN OR
Courier could be any - nolonger ALL - BUT ANY of the dates - so AN OR
PTS could be FREC3 OR FREC4 OR ACA

IS that correct?

OR(AND(A2="PTS",C2>TODAY(),D2>TODAY(),E2>TODAY()),
you need the OR and AND reversed
so the AND is A2 = PTS AND , any of the other columns so an OR

AND(A2="PTS",OR(C2>TODAY(),D2>TODAY(),E2>TODAY()))
Which means A2 MUST have PTS AND anyone of the C2,D2,E2 MUST be > then today

Thank you for your reply

Sorry was out of the office all yesterday and never had time to reply before I left Tuesday.

What you have descrived above is correct but to make it easier I have split PTS to PTS walker and PTS Non walker - like so;

If Mental Health, PMVA = Green

If HDU OR FREC3 OR FREC4 = Green

If PTS - Non Walker, FREC3 OR FREC 4 OR ACA = Green

If PTS Walker, DCA = Orange

If Courier any of them = Green

If Staff taxi any of them = Green

Is this possible if multi OR commands?

 

[etaf]

try this

 

[etaf]

Book1
	A	B	C	D	E	F	G
1	scope of work	PMVA	FREC 3	FREC 4	ACA	DCA	new rule
2	Mental health	12/25/20					green
3	HDU		12/25/20	1/23/21			green
4	PTS - Non walker				1/1/21		green
5	Staff Transport					1/1/21	green
6	Courier				1/1/21	1/1/21	green
7	PTS walker					1/1/18	orange
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=IF(AND(A2="PTS Walker",F2<>""),"orange",IF(OR(A2="mental Health",AND(A2="HDU",OR(C2>TODAY(),D2>TODAY())),AND(A2="PTS - Non Walker",OR(C2>TODAY(),D2>TODAY(),E2>TODAY())),AND(A2="Courier",OR(C2>TODAY(),D2>TODAY(),E2>TODAY(),F2>TODAY())),AND(A2="staff transport", OR(C2>TODAY(),D2>TODAY(),E2>TODAY(),F2>TODAY()))),"green",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:G	Expression	=AND($A1="PTS Walker",$F1<>"")	text	NO
A:G	Expression	=OR(AND($A1="Courier",OR($C1>TODAY(),$D1>TODAY(),$E1>TODAY(),$F1>TODAY())),AND($A1="staff transport", OR($C1>TODAY(),$D1>TODAY(),$E1>TODAY(),$F1>TODAY())))	text	NO
A:G	Expression	=OR($A1="mental Health",AND($A1="HDU",OR($C2>TODAY(),$D1>TODAY())),AND($A1="PTS - Non Walker",OR($C1>TODAY(),$D1>TODAY(),$E1>TODAY())))	text	NO

 

[Tony J]

try this

Thank you - I think you have nearly cracked it for me

I am going to leave the conditional formatting for now and return to your answer when I am happy the new way of reporting training is happening

Is there something missing on the H2:H7 range for Mental Health as that is the only one now that does not seem to be working - I cannot see B2 in the formula ... then if I try to add it messes up the other returns incorrectly

Original Formula

=IF(AND(A2="PTS",F2<>""),"orange",IF(OR(AND(A2="mental Health",B2>TODAY()),AND(A2="HDU",C2>TODAY(),D2>TODAY()),AND(A2="PTS"

My change to add Mental Health (B2)

=IF(AND(A2="PTS Walker",F2<>""),"orange",IF(OR(A2="mental Health",B2>TODAY(),AND

although as I mentione my way does not seem to work