Forum Discussion

Jennife_r's avatar
Jennife_r
Copper Contributor
Jun 06, 2024

Formatting a Date Column to be red if expired, and gold is expiring within 5 days.

I know similar things have been asked but I can't find any available solutions that I could successfully tweak to work. Whenever I think I get close the formatting just makes the dates disappear 🤦‍:female_sign:

 

I have a column for "GL Expire" dates. I need to make it so that dates before today (aka Expired) turn "Red" aka "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont"

 

And dates between today and five days from today turn "Yellow" aka "sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont sp-css-color-GoldFont".

 

The current code in there is making the expired turn red, the ones that are today turn cyan, and any others have no color.

 

How can I update this to do what I need?

 

It currently looks like this: 

 

{
  "$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": [
            "@currentField",
            ""
          ]
        },
        "",
        {
          "operator": ":",
          "operands": [
            {
              "operator": "<",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        "@currentField"
                      ]
                    }
                  ]
                },
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        "@now"
                      ]
                    }
                  ]
                }
              ]
            },
            "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont",
            {
              "operator": ":",
              "operands": [
                {
                  "operator": "==",
                  "operands": [
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            "@currentField"
                          ]
                        }
                      ]
                    },
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            "@now"
                          ]
                        }
                      ]
                    }
                  ]
                },
                "sp-css-backgroundColor-BgCyan sp-css-borderColor-CyanFont sp-css-color-CyanFont",
                {
                  "operator": ":",
                  "operands": [
                    {
                      "operator": ">",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            {
                              "operator": "toDateString()",
                              "operands": [
                                "@currentField"
                              ]
                            }
                          ]
                        },
                        {
                          "operator": "Date()",
                          "operands": [
                            {
                              "operator": "toDateString()",
                              "operands": [
                                "@now"
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    "",
                    ""
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  },
  "txtContent": "@currentField.displayValue"
}

 

 

  • Jennife_r 

    I was able to solves this using AI (after MUCH back and forth and rewording).

    The code that worked is ...

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType": "div",
      "txtContent": "@currentField",
      "attributes": {
        "class": "=if(Number(@currentField) == 0, '', if(@currentField <= @now, 'sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-CoralFont', if(@currentField <= @now + 432000000, 'sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont sp-field-fontSizeSmall sp-css-color-GoldFont', '')))"
      }
    }

    I hope this helps someone else!! 

  • Jennife_r's avatar
    Jennife_r
    Copper Contributor

    Jennife_r 

    I was able to solves this using AI (after MUCH back and forth and rewording).

    The code that worked is ...

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType": "div",
      "txtContent": "@currentField",
      "attributes": {
        "class": "=if(Number(@currentField) == 0, '', if(@currentField <= @now, 'sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-CoralFont', if(@currentField <= @now + 432000000, 'sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont sp-field-fontSizeSmall sp-css-color-GoldFont', '')))"
      }
    }

    I hope this helps someone else!! 

Resources