Forum Discussion

EmmaKate57's avatar
EmmaKate57
Copper Contributor
Oct 04, 2024

Conditional Formatting for quarters

Hi,

 

I'm trying to construct an AND function that will conditionally format a cell in Column H based on two criteria: 

If the corresponding cell in Column H says "Quarterly" and;

If the corresponding cell in Column G displays a date that occurred in an annual quarter prior to the one we're currently in. 

 

For example, if the date in column G is 6/1/2024, and Column H says "quarterly," I want the cell in Column H to be highlighted red, since 6/1/2024 was in Quarter 3, and we're currently in Quarter 4. 

 

I'm still pretty new to excel formulas, and I've tried finding a solution to this online, but none of the situations I've found solutions for are quite like mine. 

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 04, 2024

    EmmaKate57 

    Let's say you want to apply this to H2:H50.

    Select this range, H2 should be the active cell in the selection.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula


    =AND(H2="Quarterly", QUOTIENT(MONTH(G2)-1, 3)=MOD(QUOTIENT(MONTH(TODAY())-1, 3)-1, 4))

    Click Format...
    Activate the Fill tab.
    Select a fill color.
    Click OK, then click OK again.

Resources