Google-sheets – Google Sheets Conditional Formatting for expiration dates

conditional formattingformulasgoogle sheets

I need help!

I need to create conditional formatting for expiration dates:

Cells should stay green

Cells should turn yellow when 1 month of exp. date

Cells should turn red once expired

green: Date is after exact date =DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))
yellow: Is between =TODAY() =DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))
red: Less than =TODAY()

Select the range to which the rule should apply: in my example it is G1:G8.
Enter the custom formula criterion for red color: =G1<TODAY(). Note that this says "G1" because the formula is stated as it applies to the upper left corner of the range. It will be automatically adjusted for other cells in the range according to the usual rules for relative references.
Click "add another rule", enter the formula for yellow, e.g., =G1<TODAY()+90
Click "add another rule", enter the formula for green, e.g., =G1>=TODAY()+90

If several rules apply, those listed later override the earlier ones.

The result:

cells rules

FORMULAS:

RED:

=if(And(COUNTA(B42:H42)>1,isblank(D42)),true,false)

YELLOW

=if(and(istext(D42),(Or(isblank(B42),isblank(C42),isblank(E42),isblank(F42),isblank(H42)))),true,false)

GREEN

=if(counta(B42:H42)=7,true,false)