How to Set Conditional Formatting for Dates Within 2 or 4 Years in Spreadsheets
Question details
The user wants to apply conditional formatting to date cells to automatically highlight deadlines that fall within two or four years relative to the current date.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Tracking upcoming project deadlines or document retention schedules using dynamic conditional formatting rules that update based on today's date.
- Observed behavior
- Date cells need to automatically change color to orange for approaching deadlines and red for critical deadlines within a specific 24-month or 48-month timeframe.
Ensure your target cells are properly formatted as Dates rather than Text, otherwise the formula will not accurately calculate the timeframes.
Apply Conditional Formatting Using the EDATE and TODAY Functions
Use a formula-based rule within Conditional Formatting to dynamically calculate the 24-month or 48-month threshold relative to today.
The TODAY() function continuously updates to the current date, while EDATE calculates a date a specific number of months in the future. By combining these, you can create dynamic rules that automatically highlight approaching deadlines.
Highlight the date cells you want to format, such as D3:D100.
Go to the Home tab, click 'Conditional Formatting', and select 'New Rule'. Choose 'Format only cells that contain', leave the first dropdown as 'Cell Value', and select 'less than or equal to'. Enter the formula =EDATE(TODAY(),48)+25 and set the fill color to orange.
Create a second rule for the same range using the same steps. This time, enter the formula =EDATE(TODAY(),48) and set the fill color to red.
To track a 2-year timeline (e.g., in column E), repeat the process using EDATE(TODAY(),24) for your critical deadline and EDATE(TODAY(),24)+25 for your approaching deadline.
Click 'Conditional Formatting' > 'Manage Rules'. Make sure override rules, such as a green formatting rule for a 'Completed' status, are moved to the top of the list so they take precedence over the date formatting.
Manage Dynamic Date Deadlines in WPS Spreadsheets
WPS Spreadsheets fully supports advanced conditional formatting and dynamic date functions like EDATE and TODAY, allowing you to seamlessly track critical project milestones and expiration dates.
- 1. Open Your Spreadsheet: Launch WPS Spreadsheets and open the workbook containing your deadline tracker.
- 2. Select the Date Column: Highlight the column or range of dates you wish to monitor.
- 3. Apply Formatting: Navigate to Home > Conditional Formatting > New Rule and input your desired EDATE formula.
- 4. Manage Complex Rules: Use the Manage Rules option to add color scales or handle 'Completed' status overrides easily.
Frequently Asked Questions
Why are blank cells being highlighted as critical dates in my conditional formatting?
Blank cells are often interpreted by the system as zero, which equates to the year 1900. Since 1900 is less than today's date, the rule triggers. To fix this, create a new rule using the formula =ISBLANK(D3), set no formatting, place it at the top of your rules list, and check 'Stop If True'.
Can I use EOMONTH instead of EDATE for my deadlines?
Yes. If your deadlines always fall on the last day of the month, you can substitute EDATE with EOMONTH. For example, using =EOMONTH(TODAY(),48) will calculate the last day of the month exactly four years from today.
Why is my EDATE formula rule returning an error?
This usually happens when the cells are formatted as text rather than a date. Highlight the affected cells, right-click and select 'Format Cells', choose the 'Date' category, and re-enter your date values.
