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

#### maximillianrg

##### New Member
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### BobUmlas

##### Well-known Member
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

##### New Member
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

##### Well-known Member
Just change "Green" to TRUE and "White" to False, then it should work in the CF

#### Fluff

##### MrExcel MVP, Moderator

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

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

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
4
Views
59
Replies
1
Views
137
Replies
2
Views
68
Replies
3
Views
87
Replies
3
Views
34