How to Apply Conditional Formatting for Previous Quarter Dates in Excel
Question details
The user wants to highlight specific cells containing the text "Quarterly" only if the corresponding date in an adjacent column falls within the previous calendar quarter.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting financial or quarterly reports based on dynamic date ranges that automatically recognize previous quarters.
- Observed behavior
- The user needs a formula-based conditional formatting rule that correctly identifies complete quarter periods relative to the current date, rather than just matching month numbers.
Ensure your date column contains valid numeric date values rather than text strings, as formula-based conditional formatting requires true dates to calculate quarters accurately.
Use the EOMONTH Formula to Calculate the Previous Quarter
This method uses the EOMONTH and TODAY functions to dynamically calculate the exact start and end dates of the previous quarter based on the current date.
By utilizing the EOMONTH (End of Month) function, you can subtract months from the current date to establish the exact boundaries of the previous quarter. This ensures the formatting remains accurate year-round without manual updates.
Highlight the cells you want to format. For example, select H2:H50, ensuring H2 is the active cell in your selection.
Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.
Choose 'Use a formula to determine which cells to format'. In the formula box, enter: =AND(H2="Quarterly",G2>=EOMONTH(TODAY(),-6)+1,G2<=EOMONTH(TODAY(),-3))
Click the 'Format' button, switch to the 'Fill' tab, choose your desired highlight color, and click 'OK' twice to apply the rule.
Calculate Quarters Using QUOTIENT and MONTH Functions
Alternatively, use mathematical functions to extract the quarter number directly from the month value and compare it to the current quarter.
Apply Complex Conditional Formatting Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced formula-based conditional formatting, including EOMONTH, QUOTIENT, and dynamic date calculations. Easily manage and highlight your quarterly reports with our highly compatible, lightweight, and free tool.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your quarterly data.
- 2. Select the data range: Highlight the column or specific cells you want to format based on the date criteria.
- 3. Access Conditional Formatting: Go to the Home tab, click on Conditional Formatting, and select 'New Rule'.
- 4. Apply the formula: Choose the formula option, enter your EOMONTH date logic, specify your desired highlight color, and click OK.
Frequently Asked Questions
Why is my conditional formatting formula not highlighting the dates correctly?
Ensure the active cell in your selection matches the first cell referenced in your formula (e.g., if you select H2:H50, your formula must reference row 2 like H2 and G2). Also, verify your dates are formatted as actual date values, not text strings.
Can I adjust the formula to highlight the current quarter instead of the previous one?
Yes. To highlight the current quarter using the EOMONTH method, you can adjust the month offsets in the formula to: =AND(H2="Quarterly",G2>=EOMONTH(TODAY(),-3)+1,G2<=EOMONTH(TODAY(),0)).
Does conditional formatting update automatically when dates change?
Yes, conditional formatting is dynamic. Because the rule utilizes the TODAY() function, the formatting will automatically recalculate and update the highlights when the system date changes or when you modify the cell data.
