Function Problems

How to Create Green, Amber, and Red Due-Date Conditional Formatting in WPS Office Spreadsheets

Steve K Nov 13, 2026 870 views

Question details

The user wants to apply conditional formatting in WPS Office Spreadsheets to color-code due dates based on how many weeks remain before the deadline.

Create Green, Amber, and Red Due-Date Conditional Formatting in WPS Office Spreadsheets

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: Managing task or project deadlines in a worksheet and making due dates visually indicate how near they are.
Observed behavior: Due dates currently display without automatic color coding, so deadlines are not visually distinguished by whether four, three, or two weeks remain.

Before you start

Make sure your worksheet has one column for the reference or current date and one column for the due date, and that both columns use a date format before creating the rules.

Solution 1Recommended

Create Formula-Based Conditional Formatting Rules for Green, Amber, and Red Due Dates

Use this solution when you need exact week-based color thresholds and full control over rule formulas, fill colors, and the applied range.

These formulas assume column A contains the reference or current date and column B contains the due date. The difference B-A returns the number of days until the due date.

The formulas use non-overlapping day ranges: green for more than 21 days up to 28 days, amber for more than 14 days up to 21 days, and red for 14 days or fewer.

Select the due-date cells

Select the range that contains the due dates, for example B2:B24.

Open Conditional Formatting

Go to the Home tab, click Conditional Formatting, and choose New Rule.

Choose a formula-based rule

In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.

Enter the green rule

In the formula box, enter =AND($B2-$A2<=28,$B2-$A2>21), click Format, choose a green fill, and confirm.

Add the amber and red rules

Repeat the same steps with =AND($B2-$A2<=21,$B2-$A2>14) for amber and =$B2-$A2<=14 for red, applying the matching fill colors.

Create Formula-Based Conditional Formatting Rules for Green, Amber, and Red Due Dates

Rules update automatically: When the reference date or due date changes, WPS Office Spreadsheets reevaluates the formulas and updates the cell colors automatically.

Solution 2

Use Date Occurring Quick Formatting for Simple Deadline Highlights

Choose this option for fast highlighting of general date periods, such as this week or next week, when exact four-, three-, and two-week formula thresholds are not required.

Apply Green, Amber, and Red Due-Date Conditional Formatting in WPS Office Spreadsheets

WPS Office Spreadsheets supports formula-based conditional formatting, so you can create green, amber, and red due-date rules directly in your workbook while keeping full compatibility with Excel files.

1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, open your worksheet, and confirm that the reference date and due date columns are present.
2. Select the due-date range: Click and drag to select the due-date cells, such as B2:B24.
3. Create a formula-based rule: Go to Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.
4. Add the green, amber, and red rules: Enter the green rule =AND($B2-$A2<=28,$B2-$A2>21), then add the amber and red formulas with the corresponding fill colors.
5. Review the color-coded due dates: Click OK and verify that the due dates now display green, amber, or red according to the weeks remaining.

Free and lightweight spreadsheet editor with a familiar ribbon interfaceFormula-based conditional formatting with custom fill colorsFull compatibility with .xlsx and .xls Microsoft Excel filesEasy migration from Excel without losing conditional formatting rules

microsoft office alternative - wps office

Frequently Asked Questions

Can I use TODAY() instead of a reference date column in these conditional formatting rules?

Yes. In WPS Office Spreadsheets, replace $A2 with TODAY() in the formula. For example, use =AND($B2-TODAY()<=28,$B2-TODAY()>21) for the green rule.

How do I apply the green, amber, and red formatting to the entire row instead of only the due-date cell?

Select the entire row range before creating the rule, then use a formula with mixed references such as =AND($B2-$A2<=28,$B2-$A2>21). The row number updates automatically for each selected row.

Why might my rules overlap and show the wrong color?

Overlap happens when rule ranges share the same day value. Use non-overlapping ranges such as green for 22-28 days, amber for 15-21 days, and red for 14 days or fewer, or manage the rule order in Conditional Formatting > Manage Rules.

Does WPS Office Spreadsheets support Excel conditional formatting formulas?

Yes. WPS Office Spreadsheets supports Excel conditional formatting formulas and saves them normally in .xlsx workbooks, so rules created in Excel or WPS Office remain compatible.

Excel & Data Problems