Formatting Issues

WPS Spreadsheet: Highlight Dates After They Occur with Conditional Formatting

Jason Miller Nov 12, 2026 869 views

Question details

The user needs to highlight cells when their dates arrive and keep those cells highlighted after the dates have passed.

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: A date list needs conditional formatting that applies to dates from today backward, while future dates remain unformatted.
Observed behavior: The desired formatting rule should highlight dates equal to or earlier than today and exclude future dates.

Before you start

Select the full date range and identify its first cell, such as A1. Confirm that the cells contain actual date values rather than text, because the conditional formatting formula evaluates numeric dates.

Solution 1Recommended

Use a TODAY formula to highlight today’s and past dates

Use this solution when dates should remain highlighted automatically after their scheduled day has passed.

The formula =A1<=TODAY() highlights dates equal to today or earlier. As WPS Spreadsheet recalculates TODAY(), future dates become highlighted when they reach the current date. To exclude blank cells or text, use =AND(ISNUMBER(A1),A1<=TODAY()).

Select the date range

In WPS Spreadsheet, select the cells that contain the dates, such as A1:A100.

Open conditional formatting

Go to the Home tab, select Conditional Formatting, and choose New Rule or a formula-based rule option.

Enter the date formula

Choose the formula option and enter =AND(ISNUMBER(A1),A1<=TODAY()), replacing A1 with the first cell of the selected range.

Choose the highlight style

Click Format or the formatting style control, select a fill color or text color, and confirm the style.

Apply the rule

Click OK or Apply to save the rule and verify that today’s and earlier dates are highlighted while future dates are not.

Automatic date updates: The TODAY() function updates with the current date, so a future date will become highlighted automatically when it occurs. This is a live formatting rule, not a permanent recorded snapshot.

Highlight Past and Current Dates Easily in WPS Spreadsheet

WPS Spreadsheet lets you create formula-based conditional formatting rules using TODAY(), so current and past dates can be highlighted automatically while future dates remain unchanged.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
2. Select the date cells: Highlight the range that contains the dates you want to monitor.
3. Create a conditional formatting rule: Use Home > Conditional Formatting > New Rule, then select the formula-based rule option.
4. Apply the TODAY formula: Enter =AND(ISNUMBER(A1),A1<=TODAY()), choose a highlight style, and replace A1 with the range’s first cell if needed.

Create automatic date-based highlighting with conditional formattingUse formula rules to exclude blank cells and non-date textWork with familiar spreadsheet tools and a lightweight interfaceOpen and save Microsoft Excel XLSX and XLS files with broad compatibility

microsoft office alternative - wps office

Frequently Asked Questions

Which formula highlights dates that have already occurred and today’s date?

Use =A1<=TODAY() in a conditional formatting rule. Replace A1 with the first cell in your selected range.

How can I prevent blank cells from being highlighted?

Use =AND(ISNUMBER(A1),A1<=TODAY()). ISNUMBER ensures that only cells containing numeric date values meet the condition.

Will a future date stay unformatted until the date arrives?

Yes. Because TODAY() changes with the current date, a future date remains unformatted until it is equal to or earlier than today.

Why is my date rule not working in WPS Spreadsheet?

Check that the cells contain real date values rather than text, and make sure the formula references the first cell of the selected range. You can test the cell with ISNUMBER or re-enter the date using a recognized date format.

Document & File Issues