Formatting Issues

How to Add Date-Based Conditional Formatting in Microsoft Lists

Maira Mehtab Sep 29, 2026 868 views

Question details

The user needs to apply date-based conditional formatting using JSON to visually categorize upcoming deadlines in Microsoft Lists or SharePoint.

How to Add Date-Based Conditional Formatting in Microsoft Lists

Product: Microsoft Lists
Device & OS: not provided
Scenario: Tracking project deadlines and highlighting list items that are due within 30, 60, or 90 days.
Observed behavior: Dates need to be color-coded based on their proximity to the current date, but incorrect JSON formatting may cause the column values to disappear.

Before you start

Ensure you know the exact internal name of your date column, as it often differs from the display name and is required for the JSON code to work properly.

Solution 1Recommended

Apply JSON Column Formatting for Date Thresholds

Use the advanced formatting mode in Microsoft Lists to write a JSON script that compares column dates to the current date.

Microsoft Lists and SharePoint allow you to customize how fields are displayed using JSON. By comparing your date column to the '@now' parameter, you can dynamically apply background colors like red, orange, and yellow to indicate urgency.

Open Column Settings

Navigate to your Microsoft List or SharePoint list, click the header of your target date column (e.g., 'Date Until'), and select 'Column settings' followed by 'Format this column'.

Enable Advanced Mode

At the bottom of the formatting pane that appears on the right side of the screen, click on 'Advanced mode' to switch from the basic UI builder to the JSON editor.

Input the Formatting JSON

Paste your conditional formatting JSON code into the text box. Ensure that any reference to the date column uses its true internal name, not necessarily the display name.

Configure Date Thresholds

Structure your JSON logic to calculate the difference between the field value and '@now'. Assign red for values less than or equal to 30 days, orange for 60 days, and yellow for 90 days.

Save and Verify

Click 'Save' to apply the formatting. Values further out than 90 days can be left unformatted or explicitly set to green.

Troubleshooting Disappearing Data: If the date values vanish from your list after saving, this indicates a JSON syntax error or an incorrect internal column name. Column formatting only changes the display and does not delete your underlying list data.

Free Microsoft Office alternative

Manage Deadlines Easily with WPS Office

If writing custom JSON code in Microsoft Lists is too complex, you can easily track dates and apply visual rules using WPS Spreadsheet. Enjoy an intuitive interface for conditional formatting without any coding required.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and highlight the column containing your project due dates.
2. Access Conditional Formatting: Navigate to the 'Home' tab on the ribbon and click on the 'Conditional Formatting' icon.
3. Set Date Rules: Select 'Highlight Cells Rules' and choose date-specific parameters to automatically color-code approaching deadlines.

Apply conditional formatting for dates directly from a user-friendly menu.Highly compatible with Microsoft Excel (.xlsx) formats for seamless file sharing.Track project timelines and data locally without requiring complex cloud configurations.

microsoft office alternative - wps office

Frequently Asked Questions

Why did my date values disappear in Microsoft Lists after I applied formatting?

This usually happens when there is a syntax error in your JSON script or if you used the column's display name instead of its actual internal name. Double-check your code to restore the visible data.

How do I find the internal name of a column in SharePoint or Lists?

Go to the list settings via the gear icon, click on your target column, and look at the end of the URL in your browser. The internal name is the text that appears exactly after 'Field='.

Will column formatting change or delete my original list data?

No. JSON column formatting strictly alters the visual presentation (CSS/HTML styling) of the list. It does not modify, corrupt, or delete the underlying list data.

Document & File Issues