Forum Discussion
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.
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.Isn't June in the 2nd quarter of the year?
