How do I use JSON Conditional Formatting in a SharePoint Online List to color-code dates 7, 30, 90, 180 days or more out?

Question

How do I use JSON Conditional Formatting in a SharePoint Online List to color-code dates 7, 30, 90, 180 days or more out?

Crystal D. Wood 0

I have a SharePoint List that tracks when new Government Proposals are released. The $Deadline column is the RFP Release date, and the $Stage column shows whether or not we're pursuing the proposal. I color-coded the $Deadline column to know how soon the RFP would be released (1 week, 1 month, 3 months, 6 months) using the OOTB formatting to get it to work. Unfortunately, the OOTB option only gives you a "fixed date" which essentially "hard codes" the dates into the JSON (which means I have to go in every few days and manually change them in order for the color-coding to be correct).

I tried "Advanced mode" to look at the JSON code, but I don't understand JSON enough to convert the operands date string part to do the math of adding 7 days, 30 days, 90 days, 180 days...

I even looked up the whole "milliseconds" thing, but still, HOW do I get that into the JSON?

7 d = 604800000 ms

30 d = 2592000000 ms

90 d = 7776000000 ms

180 d = 15552000000 ms

OVERDUE: "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont"

1 WEEK: "sp-css-backgroundColor-BgPeach sp-css-borderColor-PeachFont sp-css-color-PeachFont"

1 MONTH: "sp-css-backgroundColor-BgGold sp-css-color-GoldFont"

3 MONTHS: "sp-css-backgroundColor-BgMintGreen sp-css-borderColor-MintGreenFont sp-css-color-MintGreenFont"

6 MONTHS: "sp-css-backgroundColor-BgCyan sp-css-color-CyanFont"

(anything else): "sp-css-backgroundColor-BgLightGray sp-css-borderColor-LightGrayFont sp-css-color-LightGrayFont"

0 comments

2 answers

Your answer

Answer 1

Hi @Crystal D. Wood,

Thanks for the documentation, it's amazing.

For RFP date column:

1.Select your RFP date column >column settings >Formatting this column >Advanced mode.

User's image

Please paste the following JSON code. Where your logic is the value between dates, so I'm not quite sure if it's >, <, >=, or <=. If it doesn't fit, you can make changes yourself.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "=if(([$Stage] == '5. Post Subm Activities' || [$Stage] == '6. Closed'),'',if((([$Status]=='Capture'||[$Status]=='Live'||[$Status]=='Temporary Hold')&&[$Deadline]),if([$Deadline]<Date(@now,toDateString()),'sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont',if([$Deadline]>Date(@now,toDateString())&&[$Deadline]<=Date((@now+ 7 * 24*60*60*1000),toDateString()),'sp-css-backgroundColor-BgPeach sp-css-borderColor-PeachFont sp-css-color-PeachFont',if([$Deadline]>Date((@now+ 7 * 24*60*60*1000),toDateString())&&[$Deadline]<=Date((@now+ 30 * 24*60*60*1000),toDateString()),'sp-css-backgroundColor-BgGold sp-css-color-GoldFont',if([$Deadline]>Date((@now+ 30 * 24*60*60*1000),toDateString())&&[$Deadline]<=Date((@now+ 90 * 24*60*60*1000),toDateString()),'sp-css-backgroundColor-BgMintGreen sp-css-borderColor-MintGreenFont sp-css-color-MintGreenFont',if([$Deadline]>Date((@now+ 90 * 24*60*60*1000),toDateString())&&[$Deadline]<=Date((@now+ 180 * 24*60*60*1000),toDateString()),'sp-css-backgroundColor-BgCyan sp-css-color-CyanFont','sp-css-backgroundColor-BgLightGray sp-css-borderColor-LightGrayFont sp-css-color-LightGrayFont'))))),'sp-css-backgroundColor-BgLightGray sp-css-borderColor-LightGrayFont sp-css-color-LightGrayFont'))"
  },
  "txtContent": "[$Deadline.displayValue]"
}

Descriptions:

(@now + 7 * 24 * 60 * 60 * 1000): get the date after calculating today + N days.

Date((@now+ 90 * 24*60*60*1000),toDateString()): get the date that doesn't include the time.

For Submission Date Column:

1.Select your Submission Date column >column settings >Formatting this column >Advanced mode.

2.Please paste the following JSON code. Where your logic is the value between dates, so I'm not quite sure if it's >, <, >=, or <=. If it doesn't fit, you can make changes yourself.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "=if(([$Stage] == '5. Post Subm Activities' || [$Stage] == '6. Closed'),'',if((([$Status]=='Capture'||[$Status]=='Live'||[$Status]=='Temporary Hold')&&[$SubmissionDate]),if([$SubmissionDate]<Date(@now,toDateString()),'sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont',if([$SubmissionDate]>Date(@now,toDateString())&&[$SubmissionDate]<=Date((@now+ 7 * 24*60*60*1000),toDateString()),'sp-css-backgroundColor-BgPeach sp-css-borderColor-PeachFont sp-css-color-PeachFont',if([$SubmissionDate]>Date((@now+ 7 * 24*60*60*1000),toDateString())&&[$SubmissionDate]<=Date((@now+ 30 * 24*60*60*1000),toDateString()),'sp-css-backgroundColor-BgGold sp-css-color-GoldFont',if([$SubmissionDate]>Date((@now+ 30 * 24*60*60*1000),toDateString())&&[$SubmissionDate]<=Date((@now+ 90 * 24*60*60*1000),toDateString()),'sp-css-backgroundColor-BgMintGreen sp-css-borderColor-MintGreenFont sp-css-color-MintGreenFont',if([$SubmissionDate]>Date((@now+ 90 * 24*60*60*1000),toDateString())&&[$SubmissionDate]<=Date((@now+ 180 * 24*60*60*1000),toDateString()),'sp-css-backgroundColor-BgCyan sp-css-color-CyanFont','sp-css-backgroundColor-BgLightGray sp-css-borderColor-LightGrayFont sp-css-color-LightGrayFont'))))),'sp-css-backgroundColor-BgLightGray sp-css-borderColor-LightGrayFont sp-css-color-LightGrayFont'))"
  },
  "txtContent": "[$SubmissionDate.displayValue]"
}

For DaysSinceSubmitted column. I don't quite understand the logic and formatting requirements of this column, could you please provide more details.

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.

Crystal D. Wood 0 Reputation points

2024-09-19T12:47:34.7633333+00:00

Where did the previous comments go? I only see your 2 answers but none of the other comments.
Ling Zhou_MSFT 23,690 Reputation points Microsoft External Staff

2024-09-20T07:03:23.5633333+00:00

Hi @Crystal D. Wood,

I can see all the comments here, and your previous comments have not been deleted. You can open the comments below the first answer, and it's likely that the comments are collapsed.
Ling Zhou_MSFT 23,690 Reputation points Microsoft External Staff

2024-09-24T01:48:06.87+00:00

Hi @Crystal D. Wood,

We have not received a response from you. Did the reply could help you? If the answer is helpful, please click "Accept Answer" and kindly upvote it. If there is any other assistance we can provide, please feel free to let us know, we will do our best to help you.
Crystal D. Wood 0 Reputation points

2024-09-24T20:25:13.1166667+00:00

I'm still working on the DaysSinceSubmitted portion. I'm trying to get the JSON to do the calculations automatically to change the display text AND do the color coding. I can use Out of the Box Conditional Formatting to get the color coding to work, but the days DO NOT CHANGE unless the record is updated. It's like the calculated column ONLY gets calculated if the record changes... That's why I have that "DaysSinceSubmitted_LastUpdatedOn" field, so I can MANUALLY go in and update the records to force the DaysSinceSubmitted to change...

This is what I have, but it is only returning the text of the FORMULA, not the RESULTS of the formula...

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "style": { "box-sizing": "border-box", "padding": "0 2px", "overflow": "hidden", "text-overflow": "ellipsis" }, "txtContent": "=if([$Stage] == '6. Closed', 0, if([$Submission Date]>=@now,0, if([$Submission Date]=@now,0, if([$Submission Date]<@now,DATEDIF([$Submission Date],@now,'D'),0))))" }

And of course, I need it color-coded:

0-179 days: "sp-css-backgroundColor-BgMintGreen sp-css-borderColor-MintGreenFont sp-css-color-MintGreenFont"

180-364 days: "sp-css-backgroundColor-BgCyan sp-css-borderColor-CyanFont sp-css-color-CyanFont"

365-729 days: "sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont sp-css-color-GoldFont"

730-1094 days: "sp-css-backgroundColor-BgPeach sp-css-borderColor-PeachFont sp-css-color-PeachFont"

= 1095 days: "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont"

ELSE: ""
Ling Zhou_MSFT 23,690 Reputation points Microsoft External Staff

2024-10-11T08:35:17.83+00:00

Hi @Crystal D. Wood,

I apologize for the late reply.

But SharePoint JSON has no way to calculate column values. It only modifies the column's display content and style.The values presented by JSON are not saved.

Answer 2

Ling Zhou_MSFT 23,690 Microsoft External Staff

Hi @Crystal D. Wood,

Thank you for posting in this community.

You can use the column formatting JSON below to calculate the difference in days between today and the deadline and modify the style of the columns. We use @now to get today's date dynamically, so we don't need to manually modify today's date.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "=if((floor((Number([$DeadLine]-Number(@now)))/(1000*60*60*24)))<0,'sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont',if((floor((Number([$DeadLine]-Number(@now)))/(1000*60*60*24)))<=7,'sp-css-backgroundColor-BgPeach sp-css-borderColor-PeachFont sp-css-color-PeachFont',if((floor((Number([$DeadLine]-Number(@now)))/(1000*60*60*24)))<=30,'sp-css-backgroundColor-BgGold sp-css-color-GoldFont',if((floor((Number([$DeadLine]-Number(@now)))/(1000*60*60*24)))<=90,'sp-css-backgroundColor-BgMintGreen sp-css-borderColor-MintGreenFont sp-css-color-MintGreenFont',if((floor((Number([$DeadLine]-Number(@now)))/(1000*60*60*24)))<=180,'sp-css-backgroundColor-BgCyan sp-css-color-CyanFont','sp-css-backgroundColor-BgLightGray sp-css-borderColor-LightGrayFont sp-css-color-LightGrayFont')))))"
  },
  "txtContent": "@currentField"
}

Here is my test result:

User's image

Our core formula for calculating days is floor((Number([$DeadLine]-Number(@now)))/(1000*60*60*24)).

To break down the calculation into pieces:

Number(…) will convert a date into a number in milliseconds.
Number([$DeadLine]-Number(@now) will take DeadLine's date in milliseconds and subtract from today’s date in milliseconds.
(10006060*24) will calculate duration of a day in milliseconds: 1000 milliseconds * 60 seconds * 60 minutes * 24 hours.
floor((Number([$DeadLine]-Number(@now)))/(10006060*24)) will take the difference between dates, divide it by duration of a day, and round the final number down to a full number.

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.

Crystal D. Wood 0 Reputation points

2024-09-16T14:58:56.3033333+00:00

Here is my original JSON. (Sorry I didn't upload earlier. New to this...) I copied your code, but it changed everything to bgCoral. When I used your code and edited the txtContent to output the core formula, EVERYTHING showed -19983, even though the $Deadline dates (@currentField) values are different.

I should have mentioned I also need to check the Status/Stage fields, too.

Proposals_RFPDate_JSON.txt
Crystal D. Wood 0 Reputation points

2024-09-16T16:27:34.8833333+00:00

I have it written two different ways. One where I hard code the date BETWEEN two HARD CODED dates (the "Proposals_RFPDate_JSON.txt" in my last comment), and one where I say if the date is between TODAY and a HARD CODED date (7, 30, 90, 180 days out...) which I've attached below. ("Proposals_SubmissionDate_JSON.txt")

This second one is less code, I think... But still I don't understand how to take the formula you provided and put it into the JSON that SharePoint spits out...
Crystal D. Wood 0 Reputation points

2024-09-16T16:34:42.2733333+00:00
I have it two different ways using OOTB Conditional Formatting:

First, with the RFP date between two hard coded dates, (but again, I have to change these EVERY DAY (so I typically only change them Monday, Wednesday, and Friday). (See my previous comment.)

Second, with the Submission date between today and a hard coded date. (Still having to update the date...) (See attached Proposals_SubmissionDate_JSON.txt file.)

I converted the OOTB Conditional Formatting in Advanced Mode, but don't understand how to copy/paste your formula so it will work. (Given that I forgot to mention the additional requirement of needing to check the Status.)

Proposals_SubmissionDate_JSON.txt
Ling Zhou_MSFT 23,690 Reputation points Microsoft External Staff

2024-09-17T06:44:31.01+00:00

Hi @Crystal D. Wood,

Thank you for your reply.

I'm a little confused by your question (I'm not even quite sure what you're trying to ask). Do you need to use conditional rule or JSON to format your columns? Because these two are different, one is the UI setup and the other needs to write JSON code. These two settings are mutually exclusive, and when one is set, the other cannot be set. Please tell me which one you finally decided to use?

Also, your JSON code is so long that it's hard for me to understand what your JSON is trying to say.

I suggest you share with me your detailed formatting rules, columns involved and column types. This will help me analyze your problem.

Thank you for your time and efforts in advance.
Crystal D. Wood 0 Reputation points

2024-09-18T15:16:32.4633333+00:00
Ling,

Thank you for your assistance up to this point. I understand that the two methods are mutually exclusive. I will try to explain better...

The images show that I'm currently using the OOTB Conditional Formatting tool, but because the only dynamic value is "Today" and all other values are fixed, I have to go in every few days and manually update my conditional formatting!

I built the Conditional Formatting using the OOTB tool to get it as close as I could and then converted it to JSON thinking it would be simple to edit, but I don't know the JSON language/syntax. That's why I included my JSON code in the attachments.

SEE NEW FILE ATTACHED: HELP_JSONvsCondFormat_SPOList_ColorCodeDates_7-30-90-180DaysOrMoreOut.pdf

This file is probably WAY more information than you wanted, but I wanted to document as much as I could...

I gave you column names; data types; choice options; screenshots of pseudo-code for the logic for columns I need help with; screenshots of my actual conditional formatting for other columns; etc.

Side comment: Why SharePoint's Conditional Formatting doesn't allow people to put in "1 week", "30 days", etc. just baffles me, but that's irrelevant. If you have any influence over features that Microsoft could build into SharePoint, how about letting users build Conditional Formatting with dynamic end dates using the tool... Or build their Conditional Formatting in Excel and then copy/paste that syntax?

Share via

How do I use JSON Conditional Formatting in a SharePoint Online List to color-code dates 7, 30, 90, 180 days or more out?

2 answers

Your answer