How to Apply Conditional Formatting for Years Less Than 5 Years Old
Question details
The user needs to apply conditional formatting to dynamically highlight cells when a given year in a column is less than five years old, and potentially stop highlighting when another specific column is filled.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking and visually highlighting records based on a rolling five-year age criteria.
- Observed behavior
- Cells need to be dynamically formatted using a custom logical formula that compares the year value in a target cell to the current year.
Ensure your target column contains four-digit year values (e.g., 2021) and that your conditional formatting range starts on the same row number referenced in your formula.
Use a Custom Formula in Conditional Formatting
Apply a custom formula using the AND and TODAY functions to dynamically evaluate the cell value against a five-year rolling window.
This solution relies on spreadsheet functions to calculate the difference between the current year and the year provided in your dataset. The TODAY() function ensures the formatting remains accurate as time passes.
Highlight the cells or rows you wish to apply the visual formatting to. For example, select your data starting from row 2.
Navigate to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.
Select the option 'Use a formula to determine which cells to format'. In the formula input box, type: =AND($B2<>"",YEAR(TODAY())-$B2<5). If you also need the highlight to disappear when a value is entered in column N, use: =AND($B2<>"", $N2="", YEAR(TODAY())-$B2<5).
Click the 'Format' button, navigate to the 'Fill' tab, choose your desired highlight color, and click 'OK' to apply the formatting rule.
Highlight Data Dynamically with WPS Spreadsheet
WPS Spreadsheet provides powerful conditional formatting tools and full support for advanced Excel formulas, making it easy to track dates, deadlines, and project statuses with visual cues.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing the date records you want to track.
- 2. Access Conditional Formatting: Highlight your data range, navigate to the 'Home' tab, and click the 'Conditional Formatting' button.
- 3. Apply your custom rule: Choose 'New Rule', select the formula option, enter your year-checking formula, and define your preferred highlight colors.
Frequently Asked Questions
Why is my conditional formatting applied to the wrong rows?
This happens when the row number in your custom formula does not match the first row of your highlighted selection. If you select rows 3 to 100, your formula must reference row 3 (e.g., $B3 instead of $B2).
Can I use this formula if my column contains full dates instead of just years?
Yes, but you will need to extract the year from your full date cells first. You can modify the formula to use the YEAR function on your cell reference: =AND($B2<>"", YEAR(TODAY())-YEAR($B2)<5).
Does the TODAY() function update automatically?
Yes, the TODAY() function is completely dynamic. It recalculates to the current date automatically every time you open the worksheet, meaning your five-year formatting window will always remain accurate without manual updates.
How do I remove conditional formatting rules I no longer need?
To remove a rule, go to the 'Home' tab, click 'Conditional Formatting', and select 'Clear Rules'. You can choose to clear rules from your currently selected cells or clear all rules from the entire worksheet.
