How to Apply Conditional Formatting for 90-Day Deadlines in Microsoft Lists
Question details
The user needs to highlight cells in Microsoft Lists in red when a submission date is missing and a specified deadline, such as 90 days from a commencement date, has passed.

- Product
- Microsoft Lists / SharePoint Online
- Device & OS
- not provided
- Scenario
- Tracking project modules and automatically flagging overdue submissions against a program commencement date in a Microsoft List.
- Observed behavior
- Standard calculated columns and basic formatting rules cannot process dynamic date comparisons combined with blank cell checks, leaving overdue items unformatted.
Ensure you have the necessary Edit or Design permissions for your Microsoft List or SharePoint site to modify column settings and apply custom JSON code.
Apply Custom JSON Column Formatting
Use SharePoint's advanced custom JSON script feature to evaluate both the empty date fields and the deadline timeframes simultaneously.
Because out-of-the-box calculated columns cannot handle complex dynamic date comparisons combined with empty cell checks, you must use SharePoint's JSON formatting capability to achieve this result. This allows you to apply conditional background colors based on logical rules.
Navigate to your Microsoft List, click the dropdown arrow next to the column header you want to format (e.g., Submission Date), select 'Column settings', and then click 'Format this column'.
In the formatting pane on the right side of the screen, scroll to the bottom and click 'Advanced mode' to switch from basic design to code input.
Write or paste a custom JSON script that checks if the submission date is blank and if today's date is greater than the commencement date plus 90 days. Within the script, set the background color attribute to red.
Click 'Save' at the bottom of the pane. The list will instantly update, turning cells red where the specific deadline conditions are met.

Track Deadlines Easily with WPS Spreadsheet
Skip the complex JSON coding required by Microsoft Lists. With WPS Spreadsheet, you can effortlessly track project modules and set up automatic color-coded alerts for overdue deadlines using a familiar, user-friendly interface.
- 1. Open your tracking sheet: Launch WPS Spreadsheet and open or create your project deadline tracking document.
- 2. Select the target column: Highlight the cells containing the submission dates that need monitoring.
- 3. Apply visual formatting: Go to the Home tab, click 'Conditional Formatting', and select 'New Rule' to easily define your overdue date logic without any code.

Frequently Asked Questions
Can I use a simple calculated column for 90-day conditional formatting?
No, calculated columns alone cannot dynamically format cell backgrounds based on today's date and empty fields simultaneously in Microsoft Lists. You must rely on advanced JSON column formatting.
Why isn't my JSON formatting script working in Microsoft Lists?
Ensure that the internal names of your columns match the ones used in your JSON script exactly. Internal names often differ from display names if they contain spaces. Also, verify that your JSON syntax is free of missing commas or brackets.
Where can I get custom JSON scripts for SharePoint lists?
The best place to request complex JSON formatting formulas is the official Microsoft Q&A forum. Make sure to use the 'SharePoint Development' tag so that script experts can provide exact code tailored to your data schema.




