Need Formula to turn cell red, or yellow if cell is < 60, 6 mos or 1 yr from due

[rogernation]

Good morning all,

I have a spreadsheet that tracks CEU's for Instructor's certification. A4; certification date, B4; CEU's, C4; Due date. 60 hrs are needed within 4 years. So basically I need a formuala that will turn B1 yellow 1 year from the due date in C4 IF there is less than 60 in B4. and red 6 mos from due date if B4 has less than 60. THis is the last part of my spreadsheet so hopefully someone can help.

In addition, does anyone know of a good site that will teach me how to do advanced formulas like this, so hopefully I can help answer someone else's question to pay this help FWD. Thank you.

[FDibbins]

Hi and welcome to the forum

Based on the sample you provided only row 6 remains white, all others turn red (if I understand you)

for the 1st rule, use this...
=AND($B4<>"",$C4<>"",C4-A4>182,$B4<60) format fill RED
Then for the 2nd rule, use this...
=AND($B4<>"",$C4<>"",C4-A4>182,$B4<60) format fill YELLOW

[FDibbins]

for the 1st rule, use this...
=AND($B4<>"",$C4<>"",C4-A4>182,$B4<60) format fill RED
Then for the 2nd rule, use this...
=AND($B4<>"",$C4<>"",C4-A4>182,$B4<60) format fill YELLOW

Im sorry, I didnt notice that both rules were the same. The 2nd rule should have been...
=AND($B4<>"",$C4<>"",YEAR($C4)-YEAR($A4)>=1,$B4<60)

Highlight the range you need to apply the CF to, then apply those 2 rules as "use formula"

[rogernation]

Good marning FDibbins, and thank you. I tried the rules you used and not sure that's what I'm after Question; What cells did you format in the rule's you provided?

Also the only column I need to turn red or yellow is column B. So if it is 6 months from the date in column c, turn column B red and if it's 1 year from the date in column C then turn column B yellow if column B has less than 60.

I think I may have confused you in my last post, so hopefully this makes a little more sence than my previous post did. Thnak you.