Conditional Formatting Formula comparing todays month with certain months-CodePudding

So I have been trying to format this conditional formatting for a while but I am having trouble writing my Or and AND Statements in the IF statement

These are the rules:

Conditional formatting = Green:

If (today's month = January OR April OR July OR October) AND G6>31%

OR If (today's month = February OR May OR August OR November) AND G6>63%

OR If (today's month = March OR June OR September OR December) AND G6>98%

Conditional formatting is Red:

If none of green conditions are fulfilled

This was the best I could come up with before getting lost on how to write it

=If((And($G$6>31%;OR((Month(Now())=Month("01-Jan-2022");(Month(Now())=Month("01-Apr-2022");(Month(Now())=Month("01-Jul-2022");(Month(Now())=Month("01-Oct-2022"))))))

Any help would be greatly appreciated

CodePudding user response：

Please check image below, its working as I have commented above,

• For TRUE --> GREEN

=OR(AND(G6>0.31,OR(MONTH(TODAY())=MONTH("1-1-22"),MONTH("1-4-22"),MONTH("1-7-22"),MONTH("1-10-22"))),
AND(G6>0.63,OR(MONTH(TODAY())=MONTH("1-2-22"),MONTH("1-5-22"),MONTH("1-8-22"),MONTH("1-11-22"))),
AND(G6>0.98,OR(MONTH(TODAY())=MONTH("1-3-22"),MONTH("1-6-22"),MONTH("1-9-22"),MONTH("1-12-22"))))=TRUE

• For FALSE --> RED

=OR(AND(G6>0.31,OR(MONTH(TODAY())=MONTH("1-1-22"),MONTH("1-4-22"),MONTH("1-7-22"),MONTH("1-10-22"))),
AND(G6>0.63,OR(MONTH(TODAY())=MONTH("1-2-22"),MONTH("1-5-22"),MONTH("1-8-22"),MONTH("1-11-22"))),
AND(G6>0.98,OR(MONTH(TODAY())=MONTH("1-3-22"),MONTH("1-6-22"),MONTH("1-9-22"),MONTH("1-12-22"))))=FALSE