How to Apply Conditional Formatting for Days Since Last Contact in Excel
Question details
The user needs to apply color-coded conditional formatting (green, yellow, red) based on the number of days that have passed since a specific date in a column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking communication or follow-ups by color-coding the time elapsed since the last contact date.
- Observed behavior
- The target cells automatically display green, yellow, or red fill colors depending on the elapsed days calculated against the current date, while ignoring blank cells.
Ensure that the dates in your target column (e.g., column J) are formatted correctly as Dates, rather than text, so the TODAY() function can calculate the differences accurately.
Use Formula-Based Conditional Formatting with the TODAY Function
Set up three separate rules using the AND and TODAY functions to assign green, yellow, and red colors based on specific day intervals.
By using the TODAY() function within conditional formatting rules, Excel can dynamically calculate the elapsed time since a given date. Wrapping the formula inside an AND function allows you to establish specific intervals (e.g., 0-3 days, 4-9 days) while preventing blank cells from triggering the format.
Highlight the range of cells you want to format. For example, if your data starts in row 2 and the date is in column J, select A2:J100 (or whichever rows contain your data).
Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule' from the drop-down menu.
Select 'Use a formula to determine which cells to format'. In the formula box, enter: =AND($J2<>"",$J2>=TODAY()-3,$J2<=TODAY()). Click the 'Format' button, choose a Green fill color from the Fill tab, and click OK.
Repeat the process by adding a second new rule. Enter the formula: =AND($J2<>"",$J2>=TODAY()-9,$J2<=TODAY()-4). Click 'Format', choose a Yellow fill color, and click OK.
Add the third rule with this formula: =AND($J2<>"",$J2<=TODAY()-10). Set the format to a Red fill color. Once all three rules are created, click 'Apply' and 'OK' in the Rules Manager.
Easily Manage Conditional Formatting and Track Dates in WPS Office
WPS Spreadsheet provides a highly intuitive interface for setting up complex conditional formatting rules like tracking days since the last contact. You can use the exact same formulas as Excel to seamlessly automate your data tracking completely free.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office, open your tracking document, and highlight the range of cells you wish to format.
- 2. Access the Conditional Formatting rules: Navigate to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.
- 3. Input the TODAY() formulas: Select 'Use a formula to format cells', paste your AND/TODAY interval formulas, assign your preferred green/yellow/red colors, and apply the rules.
Frequently Asked Questions
Why is my conditional formatting coloring blank cells red?
By default, spreadsheet software treats blank cells as a zero value, which corresponds to the date January 0, 1900. Since this is far in the past, it triggers the '10+ days' red rule. To prevent this, include the condition $J2<>"" in your formula to explicitly ignore blank cells.
Can I change the day intervals for the colors later?
Yes. You can update the day thresholds at any time by going to Home > Conditional Formatting > Manage Rules. Select the rule you want to adjust, click 'Edit Rule', and modify the numerical values subtracted from the TODAY() calculations.
Will this conditional formatting update automatically tomorrow?
Yes. The TODAY() function is volatile, meaning it automatically refreshes to the current system date every time you open or recalculate the workbook. Your color codes will always remain accurate day by day.
How do I apply the color format to the entire row instead of just the date cell?
To color the entire row, select the whole dataset range (e.g., A2:Z100) before creating the rule. Ensure you lock the column reference in the formula by putting a dollar sign directly before the column letter containing the dates (e.g., $J2).
