JSON amateur - trying to make a Microsoft list highlight overdue items

Question

Hi,

I am very new to microsoft lists and the world of JSON. I have a list that i am trying to have rows highlighted red where the column 'Due Date' is after the current date as well as having the status of anything other than CLOSED from the choice column 'Status'. But everything i have tried either highlights every row, or doesnt work with capturing the CLOSED status and they stay highlighted too. I would also like to have any rows that are due within 30 days to be highlighted yellow (also exempting any CLOSED status items but im just desperate to get the first part working. Is anyone able to help?

thank you so much!

Answer 1

Dear @Candice,

According to your request, I suggest you use Conditional Formatting, kindly follow the below steps:

In your List, choose Add view > List > Create.

Next, choose your List's name > Format current view.

In Format view > select Conditional formatting.

Then, click Add rule, choose the below features and Save.

Since multiple full row coloring is inconsistently and limitedly supported, I can help you with the red-highlight request for initial steps.

If you need to apply multiple colors, I would recommend highlighting just one column. This approach is typically the simplest and most reliable.

I hope this fulfil your request, if it does not, please share more detailed information which I could help you efficiently.

As a moderator, I do not have the tools to investigate your specific account directly because this is a user-to-user support forum. Our role is limited to providing technical support on reported issues, requests, or ideas. However, I will try my best to clarify as clearly as possible.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Use view formatting with a JSON rule that:

• Colors the whole row red when Due Date is in the past AND Status is not CLOSED.
• Colors the whole row yellow when Due Date is within the next 30 days AND Status is not CLOSED.

Assuming the internal names are DueDate and Status, and using view formatting (Format current view → Advanced mode), use this JSON:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if(and([$Status] != 'CLOSED', [$DueDate] <= @now), 'sp-field-severity--severeWarning', if(and([$Status] != 'CLOSED', [$DueDate] > @now, [$DueDate] <= @now + 30*24*60*60*1000), 'sp-field-severity--warning', ''))"
}

Explanation of the logic:

• [$Status] != 'CLOSED' excludes closed items from any highlighting.
• [$DueDate] <= @now finds overdue items (in the past) → sp-field-severity--severeWarning (red/orange style).
• [$DueDate] > @now and [$DueDate] <= @now + 30*24*60*60*1000 finds items due in the next 30 days → sp-field-severity--warning (yellow style).
• @now + 30*24*60*60*1000 adds 30 days in milliseconds.

If the field names contain spaces, use the internal name pattern (for example, a column shown as “Due Date” is usually referenced as [$Due_x0020_Date]).

To apply:

1. Open the list view.
2. Select All items > Format current view.
3. Choose Advanced mode.
4. Paste the JSON above and Save.

---

References:

• List layout customizations
• Use column formatting to customize SharePoint
• Formatting list views
• Format a column to change how it looks