Formatting Issues

How to Set Up Conditional Formatting for Inspection Due Dates in Excel

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs to configure an Excel asset register to automatically highlight approaching inspection due dates with warning colors as the deadlines draw near.

Product: Excel
Device & OS: not provided
Scenario: Tracking annual equipment or asset inspection deadlines in a spreadsheet.
Observed behavior: The user wants warning colors to trigger 30 or 60 days before the upcoming due date, rather than highlighting cells immediately based on the past inspection date.

Before you start

Before applying conditional formatting, ensure your inspection dates are formatted as valid dates in Excel rather than plain text, allowing formula calculations to process them accurately.

Solution 1Recommended

Calculate Next Due Date and Apply Warning Thresholds

Create conditional formatting rules based on the next due date using the EDATE and TODAY functions to trigger 60-day and 30-day warnings.

This approach is highly recommended as it relies on exact future due dates, making it easier to read and maintain the asset register.

Calculate the due date

Add a new column for the next due date. Assuming the last inspection date is in cell A2, use the formula =EDATE(A2,12) to automatically calculate a date exactly 12 months after the recorded inspection.

Create a new formatting rule

Select the newly created due date column. Navigate to Home > Conditional Formatting > New Rule. Choose the option 'Use a formula to determine which cells to format'.

Set the 60-day warning threshold

Enter the formula =B2-TODAY()<=60 (assuming B is your due date column). Click 'Format', choose a yellow fill color for a 60-day warning, and click OK.

Set the 30-day urgent threshold

Create a second rule for the same range using the formula =B2-TODAY()<=30. Click 'Format', choose a red fill color to signify an urgent 30-day warning, and click OK.

Adjust rule priorities

Go to Home > Conditional Formatting > Manage Rules. Ensure the most urgent color (the 30-day red warning) is at the top of the list. Check the 'Stop If True' box next to it so it takes priority over the 60-day rule.

Solution 2

Format Previous Inspection Dates Directly

If you prefer not to create a separate due date column, you can apply conditions directly to the past inspection date by calculating the months elapsed since the last inspection.

Smart Data Management

Track Inspection Deadlines Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful and intuitive conditional formatting features, allowing you to effortlessly track asset registers and inspection due dates without complex setups.

1. Open your asset register: Launch WPS Spreadsheet and open the document containing your inspection dates.
2. Access conditional formatting: Highlight your date columns and click 'Conditional Formatting' under the Home tab.
3. Apply your deadline rules: Select 'New Rule', choose the formula option, and enter your deadline calculation to trigger automated warning colors.
4. Manage rule order: Use the built-in Manage Rules dialog to instantly reorder conditions, ensuring urgent red warnings are prioritized.

Fully compatible with Microsoft Excel conditional formatting rules and functions like EDATE.Visual rule manager makes it easy to adjust warning priorities for upcoming deadlines.Free, lightweight, and fast office alternative supporting Windows, Mac, and Linux.Seamlessly import and export XLSX files with zero formatting loss.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting rule not triggering on the correct date?

This usually occurs if your dates are stored as text rather than actual date values. Ensure the column format is set to 'Short Date' or 'Long Date'. Additionally, check that your formula references use correct cell referencing (e.g., A2 instead of $A$2 if applying the rule to an entire column).

Can I highlight the entire row based on the inspection due date?

Yes. Instead of selecting just the date column, select your entire dataset before creating the rule. In the Conditional Formatting menu, choose 'Use a formula to determine which cells to format', and lock the column reference in your formula by adding a dollar sign, such as =$B2-TODAY()<=30.

What does the EDATE function do in Excel?

The EDATE function returns a date that is a specified number of months before or after a starting date. For example, =EDATE(A2, 12) calculates a date exactly 12 months after the date in cell A2, which is ideal for calculating annual inspection due dates.

How do I ensure the red warning color overrides the yellow warning?

In the Conditional Formatting menu, click 'Manage Rules'. Use the up and down arrows to move your most urgent rule (the 30-day red warning) to the very top of the list, and make sure to check the 'Stop If True' box next to it.

Document & File Issues