Forum Discussion

Naligurtan's avatar
Naligurtan
Brass Contributor
Jul 27, 2023

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

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
 
===================================================
 
{
"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"
]
}
}
}
]
}
Lists
Microsoft Lists
SharePoint Online
  • Naligurtan's avatar
    Naligurtan
    Jul 27, 2023
    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.
  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor
    Jul 27, 2023
    What's the formula in your next revision date calculated column?
      • Rob_Elliott's avatar
        Rob_Elliott
        Bronze Contributor
        Jul 27, 2023

        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)

Resources