Color-Coding SharePoint Online Library by Date Ranges

Question

Hello, folks. We're looking for a code-block to copy/paste for a hopefully simple color-coding-by-date situation in SharePoint modern/Online: a document expiration date.

We want to set a condition on a library's Next Review column (date format) where the item will turn

• Yellow if the doc's Next Review date is less than 90 days away
• *Red if the doc's Next Review date is less than 10 days away or already past (i.e., expired)

We've determined that this will require some sort of conditional formatting, probably using the "JSON" coding. We have no programmers and we know nothing of code; JSON is a complete black box to us, so we'll be blindly copy/pasting whatever chunk of code we find.

And because SP Online's interface is confusing and we've received no training or support, we'll need instructions on what values in the chunk of code we may need to tweak, as well as specifics on exactly where in the interface to paste this code.

You see, a SharePoint Online column doesn't have a single Format box like SP 2013 did. When you click the column heading and go to Column Settings > Format this Column, you get two tabs: Format View and Format Columns. The Format View tab has Choose Layout and a code box, while the Format Columns tab has Edit Styles, (conditional formatting) Manage Rules, and Advanced Mode. Unfortunately, there's no online help or documentation for SharePoint Online, so we don't know where to paste any example code that we find.

Suggestions appreciated.

Answer 1

Hi @Brian Curtis ,

You could add JSON to columns in "Advanced mode".

JSON:

{  
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",  
   "elmType": "div",  
   "txtContent": "@currentField",  
   "style": {  
      "background-color": "=if(@currentField <= @now + 864000000, 'red', if(@currentField <= @now + 7776000000, 'yellow', ''))"  
   }  
}  

1.Click your column > Column settings > Format this column > Advanced mode

2.Type your JSON codes > Save

My test result:

For Reference: Use column formatting to customize SharePoint

---

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

@Brian Curtis Looks like there is syntax error in your conditions against background-color. Try using below JSON code, it should work for you:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "background-color": "=if(@currentField <= @now, 'red', if(@currentField <= @now + 864000000, 'orange', if(@currentField <= @now + 7776000000, 'yellow', '')))" } }
Also, the numbers you are curious about are the milliseconds conversion of days you mentioned (for example: 24*60*60*1000 = 86400000) --> More explanation at: https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#formatting-items-based-on-arbitrary-dates-advanced

official documentation: https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

---

If the comment is helpful, please upvote it. For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

Answer 3

(Sorry, I was out of the office Friday.) That's perfect, exactly what we needed! Thanks so much!

Out of curiosity, why are the numbers so huge (864 million and 7.8 billion)? I was expecting to see "90" or "10" somewhere.

Answer 4

Follow-up: Expanding color-code options?

ORIGINAL CODE
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField <= @now + 864000000, 'red', if(@currentField <= @now + 7776000000, 'yellow', ''))"
}
}

My boss is, of course, never satisfied with a solution that works. Now she wants three colors for three conditions:

• Next Review <90 days from 'now' = yellow
• Next Review <10 days from 'now' = orange
• expired (Next Review >= 'now') = red

You'd think this would be simple. You'd think it would be a matter of copy/pasting a third condition into the "background color" section and changing the time and color values, right? No such luck. Here's what I tried:

NEW CODE
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField <= @now , 'red', =if(@currentField <= @now + 864000000, 'orange', if(@currentField <= @now + 7776000000, 'yellow', ''))"
}
}

Everything looks right. I even ran it through an online JSON code validator to make sure no formatting got messed up. But now the column is utterly blank.

This makes no sense. There's no conceivable way that background-color instructions, even wrong ones, could erase the contents of the cells! What's going on here?

Answer 5

Well, I see literally no difference between your code and the one I pasted. But as long as it works (and it does), I'm content. Thanks very much!