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

maximillianrg

New Member
Joined
Aug 7, 2014
Messages
40
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
 

Some videos you may like

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
Joined
Mar 14, 2002
Messages
1,170
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
Joined
Aug 7, 2014
Messages
40
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
Joined
Mar 14, 2002
Messages
1,170
Just change "Green" to TRUE and "White" to False, then it should work in the CF
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 7, 2014
Messages
40
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
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,439
Members
410,684
Latest member
LakTik
Top