Conditional Formatting with nested IFs

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.

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))