Conditional formatting based on text in one cell, date in another

Hi guys,

I'm working on a spreadsheet and would like to add some conditional formatting.

Our spreadsheet keeps track of things coming through the shop. Occasionally, there are administrative hold-ups that can get quite lengthy. I need a fast was of having these delays stand out to create greater viability to management.

Here's a brief breakdown -

The 'A' column is the date it is received into the shop.
The 'I' column is a field that is for 'sickbay' (meaning, if there is some sort of paperwork issue preventing the unit from going to the shop.). There is a drop-down list for "Yes" or "No".
The 'L' column is a place to enter notes.

What I'd like to do is add conditional formatting to the 'L' column based on the other two columns.

I think I need the formula to check the 'I' column for "Yes" or "No". If it is "Yes", then check the 'A' column. If the date is the 'A' column is older than two weeks, turn the 'L' column orange; if it's older than 30 days, turn it red.

Once something comes out of "sickbay" (the receiving clerk will change the answer "Yes" to "No" in the 'I' column), I want the formatting to back to normal.

Clear as mud, right?

Thanks in advance! I'd appreciate any help you guys could give.

The following formula should work, assuming you want your conditional formatting to start applying in row 2. Change the row number in the formulas to match your data set.
NOTE: Make sure the 14 day formula is a higher priority than the 30 day formula.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Select L2:L? and go to conditional formatting > New Rule> Use Formula

=AND(I2="Yes", ISNUMBER(A2), A2+14<= TODAY()) Format as Orange

AND(I2="Yes", ISNUMBER(A2), A2+30<= TODAY()) Format as Red
Does that work for you?