SharePoint conditional formatting rule only available for date, how to include time in the rule?

Question

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:

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

Matiur Rahman · Accepted Answer · 2020-11-13 15:41:04Z

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'))"
  }
}

Updated screenshot

edited Nov 13, 2020 at 15:41

answered Nov 13, 2020 at 14:39

Matiur Rahman

3,0672 gold badges8 silver badges8 bronze badges

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
– sharonmina

2020-11-13 15:15:21 +00:00
Commented Nov 13, 2020 at 15:15
Please see the updated response. Let me know if you have any questions

Matiur Rahman
– Matiur Rahman

2020-11-13 15:43:26 +00:00
Commented Nov 13, 2020 at 15:43
You are the man! It solved the problem. Thanks a lot matiur! Appreciate your help.

sharonmina
– sharonmina

2020-11-14 06:30:47 +00:00
Commented Nov 14, 2020 at 6:30

Add a comment |

Stack Exchange Network

SharePoint conditional formatting rule only available for date, how to include time in the rule?

1 Answer 1

Your Answer

Hot Network Questions

SharePoint conditional formatting rule only available for date, how to include time in the rule?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions