How to Highlight Past Dates When Signature Date is Blank in Excel
Question details
The user needs a way to automatically highlight specific dates in an Excel spreadsheet that are older than today, but only if an associated signature date cell is empty.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking pending document signatures, overdue tasks, or expiring items where action is required because the signature is missing.
- Observed behavior
- Users want to automatically flag expired or past dates to ensure pending signatures are visually prominent and easily trackable.
Ensure your date columns are formatted as valid date values in Excel rather than plain text, so the conditional formatting formulas can accurately calculate the differences against today's date.
Apply a Formula-Based Conditional Formatting Rule
Use the AND function combined with the TODAY() function in a conditional formatting rule to check multiple criteria simultaneously.
To evaluate multiple conditions at once in Excel, you can use the AND function inside the conditional formatting tool. This allows you to check if a target date has passed, if it contains actual data, and if a secondary cell (like a signature date) is empty.
Highlight the column containing the dates you want to check. For example, select the range in column D, ensuring cell D2 is the active cell.
Navigate to the Home tab on the ribbon, click the 'Conditional Formatting' dropdown, and select 'New Rule'.
In the New Formatting Rule dialog box, select the option labeled 'Use a formula to determine which cells to format'.
In the formula box, enter the following: =AND(D2<>"",D2<TODAY(),F2=""). This formula checks that D2 is not empty, D2 is before today's date, and F2 (the signature date cell) is empty.
Click the 'Format' button, choose your desired fill color (such as yellow or red) to highlight the past dates, and click 'OK' to apply the rule.
Easily Highlight Cells with Custom Rules in WPS Spreadsheet
WPS Spreadsheet provides a robust and intuitive Conditional Formatting tool that perfectly supports complex, multi-condition formulas like comparing dates and checking for blank cells.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx document containing the dates and signature columns.
- 2. Access Conditional Formatting: Select your date range, navigate to the Home tab, and click on the 'Conditional Formatting' button.
- 3. Create a New Rule: Choose 'New Rule', select the formula option, input your =AND() formula, and set a custom highlight color to easily track missing signatures.
Frequently Asked Questions
Why is my conditional formatting formula not working for dates?
If your formula isn't highlighting correctly, the dates might be stored as text instead of numeric values. Select the column, go to the Data tab, and use the 'Text to Columns' feature to convert them to valid date formats.
Can I highlight the entire row instead of just the date cell?
Yes. To highlight the entire row, select your whole data table instead of just the date column. Then, in your formula, lock the column references by adding a dollar sign before the column letters, like =AND($D2<>"",$D2<TODAY(),$F2="").
How do I remove or edit the conditional formatting rule later?
Go to the Home tab, click 'Conditional Formatting', and select 'Manage Rules'. From the rules manager window, you can select your existing rule to edit its formula, change the background color, or delete it entirely.
Does the TODAY() function update automatically?
Yes, the TODAY() function is highly dynamic. Every time you open the spreadsheet or trigger a recalculation, it automatically updates to the current system date, keeping your highlighted past dates fully accurate over time.
