Conditional Formatting with nested IFs

**yousafkhan1976** · 03-15-2011, 01:02 PM

I have one sheet (Sheet 1) for reporting purposes. It contains a column that uses a list with 5 status of the Charter listed.

List Name (Range) = Charter
List entries = Draft,Team Review, PMO Review, Executive Review,Approved

The second sheet labelled "Data" contains dates for each status listed above, e.g. Draft = Jan. 1, 2011, Team Review= Jan. 5, 2011, PMO Review=Jan. 15, 2011 etc.

I have also defined the above as ranges, i.e.:

CharterDraftDate,CharterTeamReviewDate,CharterPMOReviewDate,CharterExecutiveReviewDate,CharterApprovedDate

ANy help is appreciated cause I think I might go blind trying to figure this out.

I am trying to perform conditional formatting in Excel 2003 such that:

IF CurrentCell = "N/A" OR (CurrentCell="PMO Review" AND Today'sDate - CharterDraftDate > 15) OR (CurrentCell="Executive Review" AND CharterExecutiveReviewDate - CharterDraftDate >15) THEN CurrentCellBackground = RED

I tried using the following formula in conditional formating, but it does not work unless only the first condition is satisfied, i.e. CurrentCell="N/A":

=IF(OR(D3="N/A",AND(D3="PMO Review",DATEDIF(INDEX(CharterDraftDate,1,1),NOW(),"D")>15),AND(D3="Executive Review",DATEDIF(INDEX(CharterDraftDate,1,1),INDEX(CharterExecutiveReviewDate,1,1),"D")>15)),1)

**6StringJazzer** · 03-15-2011, 01:57 PM

You not have to use INDEX, just using the named range is sufficient.

DATEDIF is undocumented, and though it can be useful for some things it doesn't add much if you just want the difference in days. A simple subtraction does the same thing.

When you need to evaluate a condition such as for conditional formatting, you do not need to use IF to calculate a value. The condition itself will evaluate to TRUE or FALSE.

So your formula can collapse down to this:

=OR(D3="N/A",AND(D3="PMO Review",NOW()-CharterDraftDate>15),AND(D3="Executive Review",CharterExecutiveReviewDate-CharterDraftDate>15))

It seems like a faithful implementation of the logic you specified so maybe there's a problem with either the logic, or you are not interpreting your data correctly.

**yousafkhan1976** · 03-15-2011, 02:21 PM

Thanks for your feedback, it deifinitely helps for the pointers you gave to simplify the formula, but unfortunately it did not work for me. I am attaching the file, as maybe I did not explain things well or am interpreting the data incorrectly as stated.

One thing that was a little unclear is how do I use the same formula in subsequent cells without using INDEX. i.e. apply the same conditional formatting to check conditions of other cells in the same named range?

Appreciate your input.

**6StringJazzer** · 03-15-2011, 09:37 PM

Originally Posted by yousafkhan1976

I am attaching the file, as maybe I did not explain things well or am interpreting the data incorrectly as stated.

Looks OK to me, what test case is failing?

One thing that was a little unclear is how do I use the same formula in subsequent cells without using INDEX. i.e. apply the same conditional formatting to check conditions of other cells in the same named range?

This is why we always suggest attaching a file. From your description it wasn't evident that you needed to do this. The correct formula that will work in multiple rows would be

=OR(D3="N/A",AND(D3="PMO Review",NOW()-INDEX(CharterDraftDate,ROW(),1)>15),AND(D3="Executive Review",INDEX(CharterExecutiveReviewDate,ROW(),1)-INDEX(CharterDraftDate,ROW(),1)>15))

**yousafkhan1976** · 03-15-2011, 05:42 PM

Finally figured it out in a really long way, but achieved what I was looking to do. Created three separate Conditional formats to evaluate various conditions.