Conditional formatting with dates & statuses

[Dora16]

I have Excel 2007.
In my spreadsheet, I have, in cell F2, a "Need by Date" which can be anywhere from a few days to a few months in the future. I want to do a couple things with this based on some other cells and today's date. So in the N cells, I can have a couple different statuses - "Approved", "Awaiting Shipping", "Received" and "Shipped".

What I want is for the F2 cell to be RED if today's date has passed the date in that cell and it is not at "Received" or "Shipped".
I want the cell to stay white with no changes if today's date has passed and it is at "Received" or "Shipped".
I want the cell to be YELLOW is today's date has passed the date in the N cell and it is at "Awaiting Shipping" or "Approved" status.

I hope this makes sense!

Thanks!

 

[Steve=True]

I think you may have a slight logic problem because it could be

Past Due and Awaiting Shipping, and it would be both Yellow and Red according to your post.

Here is a sample formula, with stataus living in Column H2

=IF(AND(TODAY()>F2,OR(H2="Received",H2="Shipped")),"white",IF(AND(TODAY()>F2,OR(H2<>"Approved",H2<>"Awaiting Shipping")),"Yellow","white"))

 

[Dora16]

Oooookay, I see what you mean Steve.
If it's past due, I want "Ordered" to be yellow, "Awaiting Shipping" to be red, "Received" to be white (so I guess it can be ignored?) and "Shipped" to be yellow.

Although I am trying to make this one formula you gave me work.