0

I'm new to SharePoint. I have a SP list for my schedule. I have overdue column that I would like to change background color based on Datetime column and status. The two column are Deadline and Status. The logic is, if datetime now > Deadline (including time) and status = PENDING, change background color to red. Means that its already overdue. I managed to change the background color to red by using conditional formatting function in the SP online. But it only filter based on date not including the time. I want it to include time as well as my Deadline column is a Datetime column. Any idea on how to do this?

Here is the screenshot of my SP:

enter image description here

As you can see above, the time have not passed yet but the column color already turn red because its only filter the date.

Here is the JSON for the conditional formatting:

{
  "elmType": "div",
  "style": {
    "box-sizing": "border-box",
    "padding": "0 2px"
  },
  "attributes": {
    "class": {
      "operator": ":",
      "operands": [
        {
          "operator": "&&",
          "operands": [
            {
              "operator": "<=",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            "[$Deadline]"
                          ]
                        }
                      ]
                    }
                  ]
                },
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            "@now"
                          ]
                        }
                      ]
                    }
                  ]
                }
              ]
            },
            {
              "operator": "==",
              "operands": [
                "[$Status]",
                "PENDING"
              ]
            }
          ]
        },
        "sp-css-backgroundColor-blockingBackground50",
        {
          "operator": ":",
          "operands": [
            {
              "operator": "==",
              "operands": [
                "[$Status]",
                "COMPLETED"
              ]
            },
            "sp-css-backgroundColor-successBackground30",
            ""
          ]
        }
      ]
    }
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "line-height": "16px",
        "height": "14px"
      },
      "attributes": {
        "iconName": {
          "operator": ":",
          "operands": [
            {
              "operator": "&&",
              "operands": [
                {
                  "operator": "<=",
                  "operands": [
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            {
                              "operator": "Date()",
                              "operands": [
                                "[$Deadline]"
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            {
                              "operator": "Date()",
                              "operands": [
                                "@now"
                              ]
                            }
                          ]
                        }
                      ]
                    }
                  ]
                },
                {
                  "operator": "==",
                  "operands": [
                    "[$Status]",
                    "PENDING"
                  ]
                }
              ]
            },
            "",
            {
              "operator": ":",
              "operands": [
                {
                  "operator": "==",
                  "operands": [
                    "[$Status]",
                    "COMPLETED"
                  ]
                },
                "",
                ""
              ]
            }
          ]
        }
      }
    },
    {
      "elmType": "span",
      "style": {
        "overflow": "hidden",
        "text-overflow": "ellipsis",
        "padding": "0 3px"
      },
      "txtContent": "[$Overdue]",
      "attributes": {
        "class": {
          "operator": ":",
          "operands": [
            {
              "operator": "&&",
              "operands": [
                {
                  "operator": "<=",
                  "operands": [
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            {
                              "operator": "Date()",
                              "operands": [
                                "[$Deadline]"
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            {
                              "operator": "Date()",
                              "operands": [
                                "@now"
                              ]
                            }
                          ]
                        }
                      ]
                    }
                  ]
                },
                {
                  "operator": "==",
                  "operands": [
                    "[$Status]",
                    "PENDING"
                  ]
                }
              ]
            },
            "",
            {
              "operator": ":",
              "operands": [
                {
                  "operator": "==",
                  "operands": [
                    "[$Status]",
                    "COMPLETED"
                  ]
                },
                "",
                ""
              ]
            }
          ]
        }
      }
    }
  ]
}
0

The following is working for me (JSON applied to [Overdue]):

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@now >[$Deadline] && [$Status]=='PENDING', 'red', 'green')"
  }
}

Update for PENDINGs when [Deadline] > @now

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if((@now >[$Deadline] && [$Status]=='PENDING'), 'red', if([$Status]=='COMPLETED', 'green', 'white'))"
  }
}

enter image description here

Updated screenshot enter image description here

3
  • Thanks for your reply! But I'm trying to make those status that already passed deadline and still pending will be red color. green for completed only and for those pending status that has not passed the deadline remain white. Any help on this? I really appreciate your help. Thanks! – sharonmina Nov 13 '20 at 15:15
  • Please see the updated response. Let me know if you have any questions – Matiur Rahman Nov 13 '20 at 15:43
  • You are the man! It solved the problem. Thanks a lot matiur! Appreciate your help. – sharonmina Nov 14 '20 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.