How to Apply Excel Three-Color Formatting Only to the Last 365 Days
Question details
The user wants to apply a three-color conditional formatting scale to a range of dates, but restrict the formatting so it only applies to dates within the last 365 days, ignoring older or future dates.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying visual date highlighting using a three-color scale to analyze recent data trends without skewing the colors with irrelevant historical or future data.
- Observed behavior
- The three-color scale automatically assigns the minimum and maximum colors to all dates in the selected range, including those outside the target 365-day window, instead of excluding them.
Ensure your date column contains valid Excel serial dates rather than text strings, as conditional formatting formulas rely on numeric date calculations to function correctly.
Use Additional Conditional Formatting Rules to Exclude Dates
Since a three-color scale inherently colors all values, you can add "Stop If True" rules to clear the formatting for dates older than 365 days or in the future.
By default, Excel's three-color scale assigns colors to everything below the minimum and above the maximum thresholds. To prevent this, we create two overriding rules that strip the color from unwanted dates.
Highlight the column or range of cells containing the dates you want to format.
Go to "Home" > "Conditional Formatting" > "New Rule" and choose "Use a formula to determine which cells to format". Enter the formula =A1<TODAY()-365 (assuming A1 is the first cell in your selection). Do not set any formatting, then click "OK".
Repeat the previous step to create another new rule, this time using the formula =A1>TODAY(). Again, do not set any format and click "OK".
Navigate to "Home" > "Conditional Formatting" > "Manage Rules". Ensure your two new formula rules are at the top of the list, above your 3-color scale rule. Check the "Stop If True" box next to both formula rules and click "Apply".
Apply Custom Formula-Based Highlight Rules
Instead of using a predefined three-color scale, manually create three separate formula rules to assign specific colors strictly within your target date brackets.
Easily Apply Conditional Formatting with WPS Spreadsheet
WPS Spreadsheet provides robust, highly compatible conditional formatting tools to easily track and highlight date ranges without complex setups. It handles Microsoft Excel formatting rules seamlessly.
- 1. Open Your Document: Launch WPS Spreadsheet and select the column containing your date values.
- 2. Apply the Color Scale: Navigate to the "Home" tab, click "Conditional Formatting", and select your preferred 3-Color Scale.
- 3. Add Exclusion Formulas: Click "Conditional Formatting" > "New Rule" to add formula-based rules using =TODAY()-365 to catch dates outside your target range.
- 4. Manage and Apply: Go to "Manage Rules", arrange your exclusion formulas at the top, check "Stop If True", and click "OK".
Frequently Asked Questions
Why does my 3-color scale color blank cells?
Blank cells are treated as zeros by Excel, which mathematically corresponds to the date January 0, 1900. To fix this, add a conditional formatting rule with the formula =ISBLANK(A1), place it at the top of your rules list, and check "Stop If True".
Can I base the 365-day window on a specific date instead of today?
Yes. Instead of using TODAY(), reference a specific cell containing your target date in the formula, such as =A1<$B$1-365. Remember to use absolute references ($) for your target cell.
Is there a way to exclude outliers from a color scale automatically?
Color scales evaluate all numeric data in the selected range to determine the minimum and maximum. To exclude outliers, you cannot rely on the scale alone; you must use additional formula rules placed above the color scale rule with "Stop If True" enabled.
