Conditonal Formatting with IF Statement (Complex) Ok, here is my data set. I am

Question

Conditonal Formatting with IF Statement (Complex)

Ok, here is my data set. I am need the Assumed / Actual Date to show up this color using an if statement in the conditional formatting tool.

The light red on the 2nd table is light red because 2/1/15 hasn't occurred, but it will occur late on 2/1/15. Not inside the month of 1/1/15

Hopefully this isn't confusing. Thanks for the help! If this won't work in conditional formatting, just a nested if would work to display the colors. Then based of of that value, I can fill certain cells with the equal to function using conditional formatting. Thanks again!

Explanation / Answer

Hi..

I'll assume that the Date column is B and the Assumed/Actual Date column is C, and that the data start in row 2.

Select the cells you want to format conditionally, with the cell in row 2 as the active cell within the selection.

Create 4 conditional formatting rules of type 'Use a formula to determine which cells to format':

1) Formula: =AND(EOMONTH(C2,0)=EOMONTH(B2,0),C2<=TODAY())

Fill color: green

2) Formula: =AND(EOMONTH(C2,0)=EOMONTH(B2,0),C2>TODAY())

Fill color: light green

3) Formula: =AND(EOMONTH(C2,0)<>EOMONTH(B2,0),C2<=TODAY())

Fill color: red.

4) Formula: =AND(EOMONTH(C2,0)<>EOMONTH(B2,0),C2>TODAY()).

Fill color: light red.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.