Formatting Issues

How to Change Date Color When a Test is Overdue in Microsoft Lists

Camila Milosovich Oct 1, 2026 868 views

Question details

The user wants to automatically change the text color of a date field in Microsoft Lists when a test or item is past its expiration interval.

How to Change Date Color for Overdue Tests in Microsoft Lists

Product: Microsoft Lists
Device & OS: not provided
Scenario: Tracking test expiration dates or overdue tasks and needing a visual indicator.
Observed behavior: The system needs to visually highlight the date in red when the current date exceeds the test's validity period.

Before you start

Ensure you have 'Edit' or 'Full Control' permissions for the Microsoft List you are modifying. You will also need basic familiarity with list settings and copying JSON code.

Solution 1Recommended

Highlight Overdue Dates Using a Calculated Column and JSON

Add a helper column to calculate the expiration status, then apply JSON formatting to the original date column to change its color dynamically.

Because Microsoft Lists handles dynamic dates like 'Today' better in formatting when supported by a logical check, the most reliable method is pairing a calculated column with column formatting JSON.

Create a Calculated Column

Click 'Add column' at the end of your list, select 'More', and choose 'Calculated (calculation based on other columns)'. Name it 'IsOverdue'.

Enter the Overdue Formula

In the formula box, paste: =IF(ISBLANK(Date),"",IF(TODAY()>DATE(YEAR(Date)+2,MONTH(Date),DAY(Date)),"Yes","No")). Change 'Date' to your actual column name and replace '+2' with your specific year interval.

Open Column Formatting

Click the header of your original test Date column, navigate to 'Column settings' in the drop-down menu, and click 'Format this column'.

Apply JSON Formatting

Click 'Advanced mode' at the bottom of the formatting pane. Paste JSON code that evaluates if your 'IsOverdue' column equals 'Yes', and sets the CSS color attribute to red. Click 'Save'.

Highlight Overdue Dates Using a Calculated Column and JSON

Handling Different Intervals: If your test expiration intervals vary (e.g., 1 year for some, 3 years for others), add a separate 'Interval' number column and reference it in your formula instead of hardcoding the number 2.

Solution 2

Export to Spreadsheet and Use Built-in Conditional Formatting

If you prefer to avoid writing JSON and formulas, export your list to a spreadsheet program to use native point-and-click conditional formatting rules.

Spreadsheet Alternative

Easily Highlight Overdue Dates in WPS Spreadsheet

If writing JSON code and complex formulas in Microsoft Lists is too cumbersome, you can effortlessly track test intervals and highlight overdue dates using the built-in Conditional Formatting feature in WPS Spreadsheet. No coding is required.

1. Open your exported data: Launch WPS Spreadsheet and open your exported test tracking list (.xlsx or .csv).
2. Select the Date column: Click on the column letter (e.g., 'C') to highlight all the cells containing your test expiration dates.
3. Access Conditional Formatting: Go to the 'Home' tab on the top ribbon and click on 'Conditional Formatting'.
4. Set the Overdue Rule: Choose 'Highlight Cells Rules' > 'Less Than' and type =TODAY() in the value box.
5. Apply highlight color: Select 'Red Fill with Dark Red Text' from the formatting drop-down menu and click 'OK'.

Visual tracking for overdue tests with a simple point-and-click interface.Built-in conditional formatting rules for dynamic dates like 'Today'.High compatibility with exported Microsoft Lists and Excel formats (.xlsx, .csv).Free, lightweight, and incredibly easy to navigate.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use conditional formatting in Microsoft Lists without JSON?

Yes, Microsoft Lists offers basic conditional formatting accessible via 'Column settings' > 'Format this column' > 'Conditional formatting'. However, evaluating dates against dynamic variables like 'Today' often exceeds basic UI capabilities and requires a calculated column or JSON.

Why is my calculated column formula returning a syntax error?

Syntax errors usually occur if the column names in your formula don't perfectly match your actual list columns. Ensure that 'Date' in the formula is replaced with your exact column name, enclosed in brackets if it contains spaces (e.g., [Test Date]).

How do I highlight dates that are approaching their overdue limit?

You can modify your calculated column formula to evaluate an upcoming range. For example: =IF(AND(Date<=TODAY()+30, Date>TODAY()), "Expiring Soon", "No"). You can then apply JSON formatting to turn the background yellow when the value is 'Expiring Soon'.

Do I need to keep the calculated helper column visible?

No. Once you have set up the calculated column and your JSON code correctly references it, you can hide the calculated column from your List View. The JSON formatting on the main date column will continue to work.

Document & File Issues