JSON column formatting SPO, date-based check.

Question

Hi Everyone,

I am trying to get something to work on SharePoint Online with JSON column formatting.
What i have currently been able to achieve was, if a column is filled in ==> fill green if empty fill orange.

I would like to introduce a timebased check into this.

For example:
When filled in => Fill Green
LaunchDate reached & empty ==> fill Red
1 month or less before launch date ==> Fill orange
1 month or more before launch date =:=> fill yellow.

I am struggeling with stringing this all together.
Any ideas?

Answer 1

Hi @BramVanSchoor-6722 ，
According to my research and testing, you can format the column use Conditional formatting.
Take the Approval column as an example to test:

1,Add a calculated column:

2,Follow this order to create rules:

3,Create the follow rules:

4,Hide the 1 month column.

Then the Approval column is formatted.

And You can also change the UI operation of steps 2 and 3 to use the following JSON code:

{  
  "$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": [  
            {  
              "operator": "<=",  
              "operands": [  
                {  
                  "operator": "Date()",  
                  "operands": [  
                    {  
                      "operator": "toDateString()",  
                      "operands": [  
                        {  
                          "operator": "Date()",  
                          "operands": [  
                            "[$less_x0020_before_x0020_1_x0020_]"  
                          ]  
                        }  
                      ]  
                    }  
                  ]  
                },  
                {  
                  "operator": "Date()",  
                  "operands": [  
                    {  
                      "operator": "toDateString()",  
                      "operands": [  
                        {  
                          "operator": "Date()",  
                          "operands": [  
                            "@now"  
                          ]  
                        }  
                      ]  
                    }  
                  ]  
                }  
              ]  
            },  
            {  
              "operator": ">",  
              "operands": [  
                {  
                  "operator": "Date()",  
                  "operands": [  
                    {  
                      "operator": "toDateString()",  
                      "operands": [  
                        {  
                          "operator": "Date()",  
                          "operands": [  
                            "[$launchdate]"  
                          ]  
                        }  
                      ]  
                    }  
                  ]  
                },  
                {  
                  "operator": "Date()",  
                  "operands": [  
                    {  
                      "operator": "toDateString()",  
                      "operands": [  
                        {  
                          "operator": "Date()",  
                          "operands": [  
                            "@now"  
                          ]  
                        }  
                      ]  
                    }  
                  ]  
                }  
              ]  
            }  
          ]  
        },  
        "sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont sp-css-color-BlackText",  
        {  
          "operator": ":",  
          "operands": [  
            {  
              "operator": ">",  
              "operands": [  
                {  
                  "operator": "Date()",  
                  "operands": [  
                    {  
                      "operator": "toDateString()",  
                      "operands": [  
                        {  
                          "operator": "Date()",  
                          "operands": [  
                            "[$less_x0020_before_x0020_1_x0020_]"  
                          ]  
                        }  
                      ]  
                    }  
                  ]  
                },  
                {  
                  "operator": "Date()",  
                  "operands": [  
                    {  
                      "operator": "toDateString()",  
                      "operands": [  
                        {  
                          "operator": "Date()",  
                          "operands": [  
                            "@now"  
                          ]  
                        }  
                      ]  
                    }  
                  ]  
                }  
              ]  
            },  
            "sp-css-backgroundColor-BgBrown sp-css-color-BlackText",  
            {  
              "operator": ":",  
              "operands": [  
                {  
                  "operator": "&&",  
                  "operands": [  
                    {  
                      "operator": "==",  
                      "operands": [  
                        "[$approvaltolaunch]",  
                        ""  
                      ]  
                    },  
                    {  
                      "operator": "==",  
                      "operands": [  
                        {  
                          "operator": "Date()",  
                          "operands": [  
                            {  
                              "operator": "toDateString()",  
                              "operands": [  
                                {  
                                  "operator": "Date()",  
                                  "operands": [  
                                    "[$launchdate]"  
                                  ]  
                                }  
                              ]  
                            }  
                          ]  
                        },  
                        {  
                          "operator": "Date()",  
                          "operands": [  
                            {  
                              "operator": "toDateString()",  
                              "operands": [  
                                {  
                                  "operator": "Date()",  
                                  "operands": [  
                                    "@now"  
                                  ]  
                                }  
                              ]  
                            }  
                          ]  
                        }  
                      ]  
                    }  
                  ]  
                },  
                "sp-css-backgroundColor-BgCoral sp-css-color-BlackText",  
                {  
                  "operator": ":",  
                  "operands": [  
                    {  
                      "operator": "!=",  
                      "operands": [  
                        "[$approvaltolaunch]",  
                        ""  
                      ]  
                    },  
                    "sp-css-backgroundColor-BgMintGreen sp-css-color-MintGreenFont",  
                    ""  
                  ]  
                }  
              ]  
            }  
          ]  
        }  
      ]  
    }  
  },  
  "txtContent": "[$approvaltolaunch]"  
}  

If you have another way, please share it here.
Thank you for your understanding and support!

Thanks,
Yanli Jiang

===========================================

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.