How to Apply Conditional Formatting Based on Two Columns and Today's Date in Excel
Question details
The user needs to highlight cells in a specific column based on conditions met in two separate columns, including a comparison against today's date.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking deadlines or statuses where an item requires visual highlighting if a numeric threshold in one column is met and the date in another column has passed or is today.
- Observed behavior
- Cells in the target column dynamically change color when both the numeric condition and the date condition are simultaneously satisfied.
Ensure that the values in your target date column are formatted as actual Excel date serial numbers and not as plain text, as conditional formatting formulas rely on valid dates to work properly.
Use a Formula-Based Conditional Formatting Rule
Apply a custom formula using the AND and TODAY functions to evaluate multiple criteria across different columns simultaneously.
Using a custom formula for conditional formatting allows you to check multiple cells in the same row. By combining the AND function with the TODAY function, you can ensure both your numeric and date conditions are met before applying the highlight.
Highlight the specific range in your worksheet where you want the formatting to appear, for example, D2:D100.
Navigate to the Home tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule'.
In the New Formatting Rule dialog box, select the option that says 'Use a formula to determine which cells to format'.
In the formula input box, type: =AND($C2>=1,$D2<>"",$D2<=TODAY())
Click the 'Format' button, choose your desired fill color from the Fill tab, and click 'OK' twice to apply the rule.
Easily Manage Advanced Conditional Formatting with WPS Spreadsheet
WPS Spreadsheet provides a highly intuitive interface for setting up advanced conditional formatting rules. It supports all standard spreadsheet formulas, making it easy to highlight crucial data based on complex criteria like dynamic dates and multi-column comparisons.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your date and numeric data.
- 2. Highlight the target cells: Select the data range in your target column that you want to be conditionally formatted.
- 3. Apply the custom rule: Navigate to Home > Conditional Formatting > New Rule, select 'Use a formula', input your =AND(...) formula, and choose your highlight color.
Frequently Asked Questions
Why isn't my conditional formatting rule working on the dates?
The most common reason is that the dates are stored as text instead of valid date values. You can use the 'Text to Columns' feature or standard cell formatting options to convert them into real dates that the TODAY() function can recognize.
How can I highlight the entire row instead of just one cell?
To highlight the entire row, select your full data range (e.g., A2:E100) instead of just column D before creating the rule. Use the exact same formula: =AND($C2>=1,$D2<>"",$D2<=TODAY()). The absolute column references ($) will ensure the formatting applies across the entire row based on the values in columns C and D.
Will the TODAY() function update automatically?
Yes, the TODAY() function is volatile. Every time you open or recalculate the workbook, it automatically updates to the current system date, adjusting the highlighted cells dynamically without any manual intervention.
