Forum Discussion

AdmiralS's avatar
AdmiralS
Copper Contributor
May 11, 2020

JSON for Formatting Dates

I currently have a SharePoint list that we use for tracking Insurance Policy Expiration dates.  I would like it to format with the warning format for dates that are within the next 30 days.  I have read a few articles online and am still struggling.

 

Any help is appreciated, here is what I have so far -

 

{
"elmType": "div",
"style": {
"box-sizing": "border-box",
"padding": "0 2px"
},
"attributes": {
"class": {
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
"@currentField",
""
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "<",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-errorBackground50",
{
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-warningBackground50",
{
"operator": ":",
"operands": [
{
"operator": ">",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-successBackground50",
""
]
}
]
}
]
}
]
}
},
"children": [
{
"elmType": "span",
"attributes": {
"iconName": {
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
"@currentField",
""
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "<",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"ErrorBadge",
{
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"Warning",
{
"operator": ":",
"operands": [
{
"operator": ">",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"Accept",
""
]
}
]
}
]
}
]
},
"class": {
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
"@currentField",
""
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "<",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-color-red sp-field-iconPadding",
{
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-field-iconPadding",
{
"operator": ":",
"operands": [
{
"operator": ">",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-field-iconPadding",
""
]
}
]
}
]
}
]
}
}
},
{
"elmType": "span",
"style": {
"padding": "0 2px"
},
"txtContent": "@currentField",
"attributes": {
"class": {
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
"@currentField",
""
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "<",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-field-bold sp-css-color-red",
{
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": ">",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"",
""
]
}
]
}
]
}
]
}
}
}
]
}

Lists
SharePoint Online
  • RobElliott's avatar
    RobElliott
    Silver Contributor
    May 11, 2020

    AdmiralS wow, reading all that JSON would give me a headache. You can color-code your cells where the expiration date is within the next 30 days by using the following short JSON to add a background color. Note that it requires the date comparison to be in milliseconds! My column is named NextReview:

     

     

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$NextReview] <= @now + 2592000000, '#ff9a00','#dddeee')",
    "color": "white",
    "padding-left": "10px"
  }
}

     

     

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.

  • Vikram_Samal's avatar
    Vikram_Samal
    Steel Contributor
    May 11, 2020

    AdmiralS I will share the simpler approach just use this that will help. I have also shown below the example I made for you.

    This is the formula: You need to convert the days in to millisecond to compare. 30 days is 2592000000 milliseconds.

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if([$ExpiryDate]<= @now+2592000000, '#ff0000', '')"
  }
}

    Hopefully this helps and mark the response appropriately.

    • AdmiralS's avatar
      AdmiralS
      Copper Contributor
      May 11, 2020

      Thanks!

       

      I need to color for 3 scenarios against the same column though -

       

      Red - Dates Before Today

      Yellow- Dates between Today and 30 days out

      Green - Dates 30 days and forward from Today

       

      Can I do that all in the same JSON conditional filter?

      • Vikram_Samal's avatar
        Vikram_Samal
        Steel Contributor
        May 11, 2020

        AdmiralS Yes just you have to use multiple Ifs I have shown an example which can follow and modify as per your need.

        This is the code:

        {
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent": "@currentField",
   "style": {
      "color": "=if([$Color] == 'Red', 'red', if([$Color] == 'Green', 'green',if([$Color] == 'Blue', 'Blue', if([$Color] == 'Yellow', 'Yellow', 'Orange'))))"
   }
}

        Hope this helps mark the response appropriately if it helps to solve your problem.

Resources