How to create a RAG status in SharePoint with JSON for due dates

SharePoint: Column Conditional Formatting Using JSON

Business scenario: You have a document library containing documents that require annual reviews (or you may have a list with issues you are tracking). To visually represent items or documents that are coming up for review, a RAG status can be applied to the Review Date which will highlight when a document is either due for review or has surpassed the review date.
Example:

Prerequisites: A document library or List with a review date column of column type Date and Time.

Edit Column Formatting & Apply Code

Here is how to create a RAG status in SharePoint with JSON for due dates.

1. Select the drop-down on the date column you wish to apply conditional formatting to and select Column Settings > Format this column.

2. Select Conditional formatting.

3. And then select Advanced mode.

4. Make a note of the internal column name as we will be replacing the references with this value in the next steps.

5. Clear all the code and replace it with the below. Ensure you replace the text in yellow with your column name (e.g.$QMM_ReviewDate) you made a note of in the previous step:

{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,
“elmType”: “div”,
“txtContent”: “[$COLUMNNAME.displayValue]”,
“style”: {
“background-color”: “=if(Number([$COLUMNNAME]) == 0, ‘#ffffff’, if([$COLUMNNAME]>(@now + 5259600000), ‘#caf0cc’, if([$COLUMNNAME]>(@now + 1209600000) && [$COLUMNNAME]< (@now + 5259600000), ‘#ffebc0’, ‘#fabbc3’))”,
“box-sizing”: “border-box”,
“padding”: “0 2px”,
“overflow”: “hidden”,
“text-overflow”: “ellipsis”
},
“attributes”: {
“class”: “sp-css-backgroundColor-BgLightGray sp-field-fontSizeSmall sp-css-color-LightGrayFont”
}
}

Let’s look at what this code is doing.

• The txtContent is the value display (i.e. the date)
• All the conditions are specified in the background-color section.
• The numbers highlighted in green are how many days/months in milliseconds as JSON requires the input in this format.
  There are many useful sites on the internet you can use to calculate days or months to milliseconds. I used the following site:
  https://convertlive.com/u/convert/days/to/milliseconds

In the example above I am performing the following checks and then setting the background colour if there is a match.

a. if(Number([$ReviewDate]) == 0, '#ffffff'

Translation: IF there is no Review Date value populated, set the background to white.

b. if([$ReviewDate]>(@now + 5259600000), '#caf0cc'

Translation: IF [Review Date] is greater than (>) [Today + 2 months], set the background to green.

c. if([$ReviewDate]>(@now + 1209600000) && [$ReviewDate]< (@now + 5259600000), '#ffebc0', '#fabbc3'

Translation: IF [Review Date] is greater than (>) [Today + 2 weeks] AND IF [Review Date] is less than (<) [Today + 2 months], set the the background to yellow, otherwise for everything else, set background to red.

6. Finally, once you have applied the code and set your logic to the values you need, select Save and then Cancel to close the edit pane.

7. Your column should now display the colour formatting based on the logic you applied.

If you have more complex requirements, please don’t hesitate to get in touch.

Receive more blogs like this straight into your inbox

Sign up to receive our latest blogs and stay up to date with our latest news, Microsoft 365 updates, events, webinars and workshops.