JSON Code for a date column is equal today's date or 30 days to today's date

[Naligurtan]

Hello All,

I have a SharePoint list which as several columns. Two of them are date columns.

1-Date revised

2-Next revision date

I have a calculated Next revision date column which shows:

A-) show red if the next revision date is greater than Date revised+1 year

B-) show green for anything else

C-) Show amber if the next revision date is less than today's date-1 month or equal to today's date.

I tried to use SharePoint list formatting the out of box feature below.

However, it is not possible to implement what I am trying to achieve.

I was wondering if anyone else has a solution for this. Thanks

===================================================

{

"$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"

},

"attributes": {

"class": {

"operator": ":",

"operands": [

{

"operator": "<=",

"operands": [

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"[$Delete]"

]

}

]

}

]

},

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"@now"

]

}

]

}

]

}

]

},

"sp-css-backgroundColor-BgGold",

"sp-css-backgroundColor-BgLightGray"

]

}

},

"children": [

{

"elmType": "span",

"style": {

"line-height": "16px",

"height": "14px",

"display": {

"operator": ":",

"operands": [

{

"operator": "<=",

"operands": [

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"[$Delete]"

]

}

]

}

]

},

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"@now"

]

}

]

}

]

}

]

},

"inherit",

"none"

]

}

},

"attributes": {

"iconName": {

"operator": ":",

"operands": [

{

"operator": "<=",

"operands": [

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"[$Delete]"

]

}

]

}

]

},

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"@now"

]

}

]

}

]

}

]

},

"Error",

""

]

},

"class": {

"operator": ":",

"operands": [

{

"operator": "<=",

"operands": [

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"[$Delete]"

]

}

]

}

]

},

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"@now"

]

}

]

}

]

}

]

},

"sp-css-color-BlackText",

""

]

}

}

},

{

"elmType": "span",

"style": {

"overflow": "hidden",

"text-overflow": "ellipsis",

"padding": "0 3px"

},

"txtContent": "[$Delete.displayValue]",

"attributes": {

"class": {

"operator": ":",

"operands": [

{

"operator": "<=",

"operands": [

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"[$Delete]"

]

}

]

}

]

},

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"@now"

]

}

]

}

]

}

]

},

"sp-field-fontSizeSmall sp-css-color-BlackText",

"sp-field-fontSizeSmall sp-css-color-LightGrayFont"

]

}

}

}

]

}

[#2]

What's the formula in your next revision date calculated column?

[#3]

Next revision date=[Revised Date]+365

[#4]

@Naligurtan given that your calculated has the next revision date formula as Date revised +365 I don't quite see how your option A)  will ever be met because it will never be more than 365 days ahead of the date revised column.

But I've included it in the following JSON which you add in advanced mode and which will handle the other 2 options.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField <= addDays(@now,0), 'gold', if(@currentField > addDays([$Daterevised],365), 'red','green')",
    "color": "=if(@currentField <= addDays(@now,0), 'black', 'white')",
    "padding-left": "10px"
  }
}

Rob
Los Gallardos
Microsoft Power Automate Community Super User
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

[#5 · Accepted solution]

Thanks @rob. I think you almost got it.

It should be like this:

The Next Revision Date=Date Revised+365 days
Date Revised

If the Next Revision date is now()- 30 days : Amber
If the Next Revision date is greater than now() is: Red
Otherwise show it as Green.

[#6]

I found the solution. This works fine.


{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "[$NextRevisionDate.displayValue]",
"style": {
"background-color": "=if([$NextRevisionDate] > @now, '#FF0000', if([$NextRevisionDate]<(@now-2592000000), '#008000', '#ff8204'))",
"box-sizing": "border-box",
"padding": "0 2px",
"overflow": "hidden",
"text-overflow": "ellipsis"
},
"attributes": {
"class": "sp-css-backgroundColor-BgLightGray sp-field-fontSize13 sp-css-color-white"
}
}