Column Formatting Help

Occasional Contributor

Hello,

I am trying to get some column formatting working but struggling.

 

All i basically want is Today = Yellow Background, Tomorrow = Blue Background, everything else unformatted. Obviously the Today is quite easy and done via the wizard. For Tomorrow I have tried adding milliseconds to now, but without any luck. Would appreciated any help.

 

Field is called StartDate

 

{
  "elmType": "div",
  "style": {
    "box-sizing": "border-box",
    "padding": "0 2px"
  },
  "attributes": {
    "class": {
      "operator": ":",
      "operands": [
        {
          "operator": "==",
          "operands": [
            {
              "operator": "Date()",
              "operands": [
                {
                  "operator": "toDateString()",
                  "operands": [
                    {
                      "operator": "Date()",
                      "operands": [
                        "[$StartDate]"
                      ]
                    }
                  ]
                }
              ]
            },
            {
              "operator": "Date()",
              "operands": [
                {
                  "operator": "toDateString()",
                  "operands": [
                    {
                      "operator": "Date()",
                      "operands": [
                        "@now"
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        },
        "sp-css-backgroundColor-warningBackground50",
        {
          "operator": ":",
          "operands": [
            {
              "operator": "==",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            "[$StartDate]"
                          ]
                        }
                      ]
                    }
                  ]
                },
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            "(@now + 86400000)"
                          ]
                        }
                      ]
                    }
                  ]
                }
              ]
            },
            "sp-css-backgroundColor-blueBackground37",
            ""
          ]
        }
      ]
    }
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "line-height": "16px",
        "height": "14px"
      },
      "attributes": {
        "iconName": {
          "operator": ":",
          "operands": [
            {
              "operator": "==",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            "[$StartDate]"
                          ]
                        }
                      ]
                    }
                  ]
                },
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            "@now"
                          ]
                        }
                      ]
                    }
                  ]
                }
              ]
            },
            "",
            {
              "operator": ":",
              "operands": [
                {
                  "operator": "==",
                  "operands": [
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            {
                              "operator": "Date()",
                              "operands": [
                                "[$StartDate]"
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            {
                              "operator": "Date()",
                              "operands": [
                                "(@now + 86400000)"
                              ]
                            }
                          ]
                        }
                      ]
                    }
                  ]
                },
                "",
                ""
              ]
            }
          ]
        }
      }
    },
    {
      "elmType": "span",
      "style": {
        "overflow": "hidden",
        "text-overflow": "ellipsis",
        "padding": "0 3px"
      },
      "txtContent": "[$StartDate.displayValue]",
      "attributes": {
        "class": {
          "operator": ":",
          "operands": [
            {
              "operator": "==",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            "[$StartDate]"
                          ]
                        }
                      ]
                    }
                  ]
                },
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            "@now"
                          ]
                        }
                      ]
                    }
                  ]
                }
              ]
            },
            "",
            {
              "operator": ":",
              "operands": [
                {
                  "operator": "==",
                  "operands": [
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            {
                              "operator": "Date()",
                              "operands": [
                                "[$StartDate]"
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            {
                              "operator": "Date()",
                              "operands": [
                                "(@now + 86400000)"
                              ]
                            }
                          ]
                        }
                      ]
                    }
                  ]
                },
                "",
                ""
              ]
            }
          ]
        }
      }
    }
  ]
}

 

4 Replies

@Gristy  I am a little short on time, but this might help. The code below formats the item background red if the date is less than 30 days from now otherwise it leaves the background white.

 

Hope this helps :)

 

{
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField >= @now + 2592000000 || @currentField <= @now - 62208000000,'white', '#ffa59b')"
}
}

 

Hi Steve

Thank you but that is not what is required

Today is yellow and tomorrow is blue

Everything else no formatting


Cheers

Chris

@Gristy you can do it with the following JSON:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField == Date(@now,toDateString()),'#f4b846', if(@currentField > @now && @currentField <= (@now + 86400000),'#80c1c5','white')"
  }
}

 

which gives this result:

Today and Tomorrow.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

thank you so much you have made this look a lot easier !