Conditional Formatting with "AND" or "IF" or both types of formulas

[Rosstamon]

Hello,
In my spreadsheet in column F I have text which indicates that a line is to Renew Annually otherwise it's just equipment. If renewable then Column G is the Renewal Due Date (otherwise is just says "N/A") and Column H will be blank, or will indicate either “Done” for renewable lines that have been completed or “N/A” for lines that are not renewable.
Assumptions:
Let’s assume today is 04/02/2014
Cell F27 says – “Renew Annually”
Cell G27 (which is the Renewal Due Date) is “04/15/2014”
Cell H27 is blank
I would like to employ conditional formatting in column F to look at columns G and H and do the following:
I. If today’s date is within 15 days less-than or equal to G27, AND H27 does not say “Done” or “N/A” then turn cell F27 yellow.
II. If the line above (I) above is the same except H27 says “Done” or “N/A” then don’t change color
III. If today’s date is after the date in G27, AND H27 does not say “Done” or “N/A” then turn Cell F27 red.
IV. If the line above (III) is the same except H27 says “Done” or “N/A” then don’t change color

I hope someone can help and I really appreciate the time you've already spent just reading this far.
Hope to hear from someone soon.
Thanks again,
R

 

[Marcelo Branco]

Maybe...

I. try this formula
=AND($F27="Renew Annually",TODAY()=MEDIAN($G27-15,TODAY(),$G27),$H27<>"Done",$H27<>"N/A")
Fill --> yellow

III. try this formula
=AND($F27="Renew Annually",TODAY()>$G27,$H27<>"Done",$H27<>"N/A")
Fill --> red

Hope this helps

M.

 

[bclaredal]

You could add another column with the following formula: =IF(OR(H3="done",H3="n/a"),0,IF(AND(ISBLANK(H3),G3-TODAY()<1),2,IF(AND(ISBLANK(H3),G3-TODAY()<=15,1,3)))

Then do conditional formatting on this new column where you choose "format only cells that contain" and set the numbers 0, 1, 2, 3 in the following order:

0 =Green
1 = Yellow
2 =Red
3 = Green

 

[Rosstamon]

Thank you Marcelo Branco. It took me a while to respond because at first this didn't work for me. I worked on the formulas some and figured out how to make it work for me. Your help is most appreciated. I don't know how I would be able to figure this stuff out if it wasn't for the skill of people like you on this message board. Thank you thank you thank you.

 

[Rosstamon]

bclaredal: I'm sure your solution would have worked but I didn't want to add another column to my spreadsheet if it wasn't needed, and after working with the solution offered by Marcelo I was able to make it work like I wanted. Your help is most appreciated though. I am very grateful to have the assistance of all of you experts on this board when I find myself stumped in excel.

 

[Marcelo Branco]

Thank you Marcelo Branco. It took me a while to respond because at first this didn't work for me. I worked on the formulas some and figured out how to make it work for me. Your help is most appreciated. I don't know how I would be able to figure this stuff out if it wasn't for the skill of people like you on this message board. Thank you thank you thank you.

You are very welcome! Thanks for the feedback.

M.