Others

How to Apply Conditional Formatting in Microsoft Lists for Overdue Tasks

Aamir Naveed Akram Oct 9, 2026 868 views

Question details

The user wants to automatically highlight overdue items in Microsoft Lists when the due date has passed and the task is not marked as completed.

How to Apply Conditional Formatting in Microsoft Lists Based on Due Date and Status

Product: Microsoft Lists
Device & OS: not provided
Scenario: Tracking project tasks or deadlines and needing a visual indicator for items that require immediate attention.
Observed behavior: The user needs to implement JSON column formatting to dynamically change row or column colors based on specific date and status conditions.

Before you start

Ensure you have Edit permissions for the Microsoft List you wish to modify and note the exact internal names of your 'Due Date' and 'Status' columns.

Solution 1Recommended

Apply JSON Column Formatting for Overdue Items

Use Microsoft Lists' advanced formatting feature to write a JSON rule that checks the date against today and verifies the completion status.

Microsoft Lists allows you to customize how data is displayed using JSON syntax. You can apply this code to a specific column or an entire row to make overdue items stand out immediately when users open the list.

Open Column Settings

Navigate to your Microsoft List, click the drop-down arrow next to the column header you want to format (e.g., 'Due Date'), and select 'Column settings' followed by 'Format this column'.

Switch to Advanced Mode

At the bottom of the formatting pane on the right side of the screen, click on the 'Advanced mode' link to enable direct JSON input.

Input JSON Rule

Paste your JSON code. The code should use an 'if' operator to evaluate whether the due date (`[$DueDate]`) is less than or equal to `@now` AND the status (`[$Status]`) is not equal to 'Completed'.

Save and Test

Click the 'Save' button. Test your list by verifying that current items, future items, and completed items display the correct background colors.

Apply JSON Column Formatting for Overdue Items

Verify Internal Column Names: Make sure to use the exact internal column names in your JSON code. If a column name has spaces, its internal name might look like `[$Due_x0020_Date]` instead of `[$DueDate]`.

Free Microsoft Office alternative

Track Tasks Easily with WPS Spreadsheet

While Microsoft Lists requires complex JSON code for conditional formatting, WPS Spreadsheet offers an intuitive, visual interface for managing tasks, dates, and statuses. Easily highlight overdue items with built-in formatting rules—no coding required. WPS Office is a free, lightweight alternative that is highly compatible with Microsoft Excel formats.

1. Create Your Task List: Open WPS Spreadsheet and set up your columns for 'Task Name', 'Due Date', and 'Status'.
2. Open Conditional Formatting: Highlight the cells you want to format, navigate to the 'Home' tab, and click on 'Conditional Formatting'.
3. Set Visual Rules: Select 'New Rule' and choose standard formula conditions to automatically highlight rows where the date is past and the status is pending.

Visual conditional formatting without JSON codingFully compatible with Microsoft Excel (.xlsx) filesLightweight application with a familiar, user-friendly interfaceFree built-in templates for project management and task tracking

microsoft office alternative - wps office

Frequently Asked Questions

Can I highlight the entire row instead of just one column in Microsoft Lists?

Yes. To format the entire row, click on the view drop-down (usually labeled 'All Items') at the top right of the list, select 'Format current view', choose 'Advanced mode', and apply your JSON formatting rule there.

Why is my JSON formatting not working for the date column?

This typically occurs because the internal column name referenced in your code is incorrect. Check your list settings to find the exact internal name for your Due Date column, and ensure you are using `@now` to reference the current date.

Do I need coding experience to use Microsoft Lists formatting?

While basic color-coding for a single status can be done using the standard user interface, applying multi-condition formatting (like checking both Date and Status simultaneously) requires a basic understanding of JSON syntax and operators.

Office Quick Solutions