Help with complex if and or to then be used in conditional formatting

[maximillianrg]

Hello Excel Masters

I'm trying to accomplish two things and am hoping you can help me with both. Cell A1 will contain one of 5 values and those values are P0 P1 P2 P3 P4 P5 and cell C1 will contain a date.

Part 1
The first think I'm trying to do is write an if and or statement that basically says if any of the combined conditions below are true return the word "Green" otherwise return the word "white"

A1="P0", C1=TODAY(),
A1="P1", C1=TODAY(),
A1="P1", C1=TODAY()-1,
A1="P2", C1=TODAY(),
A1="P2", C1=TODAY()-1,
A1="P2", C1=TODAY()-2,
A1="P3", C1=TODAY(),
A1="P3", C1=TODAY()-1,
A1="P3", C1=TODAY()-2,
A1="P3", C1=TODAY()-3,
A1="P4", C1=TODAY(),
A1="P4", C1=TODAY()-1,
A1="P4", C1=TODAY()-2,
A1="P4", C1=TODAY()-3,
A1="P4", C1=TODAY()-4,
A1="P5", C1=TODAY(),
A1="P5", C1=TODAY()-1,
A1="P5", C1=TODAY()-2,
A1="P5", C1=TODAY()-3,
A1="P5", C1=TODAY()-4,
A1="P5", C1=TODAY()-5,

Part 2 is I then want to take that formula and drop it into a conditional formatting rule so that if any of the above combined conditions are true, it turns cell C1 green and if false it does nothing. Please not that once we get this working for cell C1 I need to do a fill down for about 100 more rows. Thank you in advance for your help

 

[BobUmlas]

Enter something like this in the worksheet, say in I1:J21
P0 =I1&TODAY()
P1 =I2&TODAY()
P1 =I2&TODAY()-1
etc.

Then use this formula on C1 for the conditional formatting:
=ISNUMBER(MATCH(A1&C1,$J$1:$J$21,0))
and format it green.

Note that cell J1 might look like P044071, but that's fine -- it's a serial # for 8/28/20

 

[maximillianrg]

The below formula returns the text "Green" if any of the conditions are true and "White of it is not. Now I just need help converting it so it returns true of false so can use it as a conditional formatting formula

=IF(AND(A1="P0",D1=TODAY()),"Green",IF(AND(A1="P1",D1=TODAY()),"Green",IF(AND(A1="P1",D1=TODAY()-1),"Green",IF(AND(A1="P2",D1=TODAY()),"Green",IF(AND(A1="P2",D1=TODAY()-1),"Green",IF(AND(A1="P2",D1=TODAY()-2),"Green",IF(AND(A1="P3",D1=TODAY()),"Green",IF(AND(A1="P3",D1=TODAY()-1),"Green",IF(AND(A1="P3",D1=TODAY()-2),"Green",IF(AND(A1="P3",D1=TODAY()-3),"Green",IF(AND(A1="P4",D1=TODAY()),"Green",IF(AND(A1="P4",D1=TODAY()-1),"Green",IF(AND(A1="P4",D1=TODAY()-2),"Green",IF(AND(A1="P4",D1=TODAY()-3),"Green",IF(AND(A1="P4",D1=TODAY()-4),"Green",IF(AND(A1="P5",D1=TODAY()),"Green",IF(AND(A1="P5",D1=TODAY()-1),"Green",IF(AND(A1="P5",D1=TODAY()-2),"Green",IF(AND(A1="P5",D1=TODAY()-3),"Green",IF(AND(A1="P5",D1=TODAY()-4),"Green",IF(AND(A1="P5",D1=TODAY()-5),"Green","White")))))))))))))))))))))

 

[BobUmlas]

Just change "Green" to TRUE and "White" to False, then it should work in the CF

 

[Fluff]

You could also use
=OR(AND(A1="P0",D1=TODAY()),AND(A1="P1",OR(D1=TODAY(),D1=TODAY()-1)),AND(A1="P2",D1>=TODAY()-2,D1<=TODAY()),AND(A1="P3",D1>=TODAY()-3,D1<=TODAY()))

Just expand it to include P4 & P5

 

[maximillianrg]

Hello Fluff - Your formula is genius and works amazingly well and is so much shorter then what I had - Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.