This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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"
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(147.20000000000002, 82%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELZ%3C/text%3E%3C/svg%3E)
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:
Our core formula for calculating days is floor((Number([$DeadLine]-Number(@now)))/(1000*60*60*24)).
To break down the calculation into pieces:
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.
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.
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...
I have it two different ways using OOTB Conditional Formatting:
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
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.
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...
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.
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.
Where did the previous comments go? I only see your 2 answers but none of the other comments.
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.
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.
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:
= 1095 days: "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont"
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.