SOLVED

Formatting Dates in SharePoint Online list

Copper Contributor

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 make the "Exp Date" Green if a date is entered and it is not within 30 days of expiring.  If the "Exp Date" is 30 days or less of expiring make it Red.

5 Replies

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

@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",

@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"
              ]
            },
            ""
          ]
        },
        ""
      ]
    }
  }
}

 

 

best response confirmed by spinman (Copper Contributor)
Solution
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
1 best response

Accepted Solutions
best response confirmed by spinman (Copper Contributor)
Solution
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

View solution in original post