Forum Discussion

Tin Contributor

Dec 16, 2019

Solved

Formatting Dates in SharePoint Online list

I have a list with two dates one labeled "Effective Date" and one labeled "Exp Date". Both columns are using the Date & Time column type but set to Date Only. What I'm trying to figure out is how to...

Lists

SharePoint Online

Robin Nilsson
Dec 17, 2019
The logic in the JSON doesn't seem to match what you asked for - what it is doing is showing Effective Date green if the Effective Date is less than ExpDate+1month, red if it's after that.
You seem to want Effective Date green if the ExpDate is less than a month away, and red if it's after the ExpDate... That's more complex logic. I'm not that great in JSON, but in pseudo logic it would be something like

If EffectiveDate < ExpDate and EffectiveDate >= ExpDate-1month show green
If Effective Date > ExpDate and EffectiveDate <= ExpDate+1month show red

spinman

Tin Contributor

Dec 17, 2019

Robin Nilsson I tried to use the JSON provided but SharePoint gives me an error that says "Please enter valid column-formatting JSON.

Robin Nilsson

Bronze Contributor

Dec 17, 2019

spinman There's an error in the $schema code. Change this:

"$schema": "<a href="<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>" target="_blank"><a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a</a>>",

to this:

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

spinman

Tin Contributor

Dec 17, 2019

Robin Nilsson Thanks. So I made that change and it let me save it. However, the Exp Date doesn't show up unless you go into the record and the field is not colored red or green. Here is what I put in the JSON format field of the Exp Date field. Am I still missing something?

{
  "$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": {
      "operator": "?",
      "operands": [
        "=length([$EffectiveDate]) > 0",
        {
          "operator": "?",
          "operands": [
            {
              "operator": "<",
              "operands": [
                "[$EffectiveDate]",
                "[$ExpDate]"
              ]
            },
            "",
            {
              "operator": "?",
              "operands": [
                {
                  "operator": "<",
                  "operands": [
                    "[$EffectiveDate]",
                    {
                      "operator": "+",
                      "operands": [
                        "[$ExpDate]",
                        2592000000
                      ]
                    }
                  ]
                },
                "green",
                "red"
              ]
            },
            ""
          ]
        },
        ""
      ]
    }
  }
}

Robin Nilsson
Bronze Contributor
Dec 17, 2019
The logic in the JSON doesn't seem to match what you asked for - what it is doing is showing Effective Date green if the Effective Date is less than ExpDate+1month, red if it's after that.
You seem to want Effective Date green if the ExpDate is less than a month away, and red if it's after the ExpDate... That's more complex logic. I'm not that great in JSON, but in pseudo logic it would be something like

If EffectiveDate < ExpDate and EffectiveDate >= ExpDate-1month show green
If Effective Date > ExpDate and EffectiveDate <= ExpDate+1month show red