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)