How to Highlight an Invoice Due Date After Seven Days in Excel
Question details
The user wants to automatically highlight a cell containing an invoice due date in a specific color (red) if it is more than seven days past the invoice completion date.
- Product
- Excel / Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking invoice payments and identifying overdue invoice dates visually using a spreadsheet.
- Observed behavior
- The goal is to apply a conditional formatting rule that compares a due date cell against a completion date cell and changes the cell color based on a 7-day interval.
Ensure that your completion date and due date columns are formatted correctly as Date values rather than plain text. Identify the specific column letters for both dates before applying the conditional formatting rule.
Use a Custom Conditional Formatting Formula
Apply a custom formula in the conditional formatting rules to highlight overdue dates automatically.
Using a custom formula allows you to compare two cells directly. By checking if the due date is greater than the completion date plus seven days, Excel will automatically flag the target cells.
It is important to use absolute column references (like $G and $E) so the formula accurately checks the correct columns even if you apply the rule to a wider range.
Highlight the cell or range of cells in your due date column (for example, click cell G6, or select the range starting from G6 in column G).
Go to the Home tab on the top ribbon, click on Conditional Formatting in the Styles group, and select New Rule from the dropdown menu.
Choose 'Use a formula to determine which cells to format'. In the formula box, enter: =$G6>$E6+7 (Make sure to replace G6 with your actual due date cell and E6 with your completion date cell).
Click the Format button, navigate to the Fill tab, choose a red background color, and click OK twice to apply the rule to your spreadsheet.
Highlight Overdue Invoice Dates Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced conditional formatting and custom formulas. You can easily manage your invoices, track overdue payments, and format data seamlessly within a highly compatible interface.
- 1. Open your invoice tracker in WPS Spreadsheet: Launch WPS Office and open your .xlsx or .xls file containing the invoice dates.
- 2. Access Conditional Formatting: Select your due date column, navigate to the Home tab, and click on Conditional Formatting > New Rule.
- 3. Apply the date formula: Select 'Use a formula', input =$G6>$E6+7, click Format to set your preferred red fill color, and save the rule.
Frequently Asked Questions
Why is my conditional formatting highlighting blank cells in red?
Spreadsheets often treat blank cells as a zero value, which can trigger date comparison formulas unexpectedly. To fix this, wrap your condition in an AND formula that explicitly checks for blanks: =AND($G6<>"", $E6<>"", $G6>$E6+7).
Can I highlight the entire row instead of just the date cell?
Yes. Select the entire data range for your table (e.g., A6:G100) before creating the rule, and ensure your formula uses absolute column references (like $G6 and $E6). Because the column is locked with the dollar sign, the condition will be evaluated based on those specific columns, applying the red fill to the whole row when met.
How do I change the highlight color if the invoice is paid?
You can add a second conditional formatting rule that checks a 'Status' column. If the status cell equals 'Paid', set the format to a green fill. Go to 'Manage Rules' to ensure this new rule is placed at the top of your rules list with 'Stop If True' checked.
Will this formula update automatically based on today's date?
The formula =$G6>$E6+7 compares two fixed dates within your sheet. If you want to highlight dates based on the current date rather than a fixed completion date, you should use the TODAY() function. For example, to highlight due dates that are 7 days past today, use =TODAY()>$G6+7.
