“How can I apply some additional format to the SharePoint date calculation, for example to change colours or hide negative values?”
As already described in a previous article, you shouldn’t do daily updates to calculate with today’s date in SharePoint. You can use the JSON column formatting instead to do the calculations that’ll recalculate with every load of the page. But the article describes only the basic calculation, what about some formatting? How to change colour based on the value? Or how to hide negative values?
This article will show you a few examples how to format the column.
Hide negative values
Since the outcome of the calculation using JSON formatting is not stored anywhere, you must use the calculation in every condition. That means, if you want to check if the value is less than 0, you must repeat the calculation.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24))",
"style": {
"display": "=if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) < 0, 'none', 'inherit')"
}
}That’s two calculations. One calculation in the “txtContent” to display the value, and another one to set if the value should be visible. It’s using the “style” property where you set the “display” value depending on the result. If the calculation is less than 0, use “display”: “none” (hide), otherwise “display”: “inherit” (show).
Add colour depending on the value
Another example is to add colours depending on the calculated value. For example, use green colour if it’s > 50, orange if it’s > 20 and red for the rest.
The colour is defined in the “style” property, but this time use “color” instead of “display”.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24))",
"style": {
"color": "=if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) > 50, '#00FF00', if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) > 20, '#FFA500', '#ff0000'))"
}
}Again, you can see that the calculation is repeated multiple times to check the result.
if <calculation> > 50?
true = use green
false = if <calculation> > 20?
true = use orange
false = use red
You can combine it also with the previous example to hide the negative values, in this case the ones that don’t have a date.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24))",
"style": {
"color": "=if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) > 50, '#00FF00', if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) > 20, '#FFA500', '#ff0000'))",
"display": "=if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) < 0, 'none', 'inherit')"
}
}
All the colours are in the hex format, which you can easily get using google. Just type in “hex color”, pick your colour and take its hex value.
Use predefined classes for the format
The last example will use the predefined SharePoint styles, whose list you can find here. If you don’t want to define each property separately, you can use a class. But when using a class you don’t use “style” attribute anymore. You’ll use the “attribute” property with “style” property inside.
Doing the same as before, but this time using classes:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24))",
"attributes": {
"class": "=if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) > 50, 'sp-css-backgroundColor-BgMintGreen sp-css-borderColor-MintGreenFont', if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) > 20, 'sp-css-backgroundColor-warningBackground40 sp-css-color-GoldFont', 'sp-css-backgroundColor-BgCoral sp-css-color-CoralFont'))"
}
}And combined with the hiding of empty values:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24))",
"attributes": {
"class": "=if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) > 50, 'sp-css-backgroundColor-BgMintGreen sp-css-borderColor-MintGreenFont', if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) > 20, 'sp-css-backgroundColor-warningBackground40 sp-css-color-GoldFont', 'sp-css-backgroundColor-BgCoral sp-css-color-CoralFont'))"
},
"style": {
"display": "=if(floor((Number([$Next_x0020_date])-Number(@now))/(1000*60*60*24)) < 0, 'none', 'inherit')"
}
}
In this example I used some random classes, but using the link above you can pick any class you want.
Summary
You don’t have to stop with just a number when doing date calculation in SharePoint, you can also format it. This post describes three basic formatting options – hide the number, add a colour to the number, or add a whole style class. Combine it together with a condition depending on the result value and your lists can be much easier to read.
Just remember that you must always use the column internal name!