conditional formatting based on status of the field and date sharepoint

Question

im trying to do conditional formatting in ms list based on status and date. These 2 are in different columns(names of the columns Milestone 1 Due Date and Milestone 1 Status. I have a 4 possible statuses ( Complete, On Track, Delayed, Not Started) of a milestone 1 and Due dates are just dates. Id like to show in blue color the dates of all tasks which have status Complete, green color status On Track but the date is still in future, if the status is On Track and date in near future (within 5 days) id like to show it as Yellow color, If status is On Track and date is in past id like to show the date as red. If the Status of milestone 1 is Delayed id like to show the date as Yellow color but only if the date is in past and Not Started status grey.

Answer 1

To achieve the desired conditional formatting in SharePoint, you can use column formatting. You can apply different classes to the date column based on the values in the Milestone 1 Status column. Here's an example of how you can achieve this:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "background-color": {
      "operator": "?",
      "operands": [
        {
          "operator": "==",
          "operands": [
            "[$Milestone_x0020_1_x0020_Status]",
            "Complete"
          ]
        },
        "blue",
        {
          "operator": "?",
          "operands": [
            {
              "operator": "==",
              "operands": [
                "[$Milestone_x0020_1_x0020_Status]",
                "On Track"
              ]
            },
            {
              "operator": "?",
              "operands": [
                {
                  "operator": "<=",
                  "operands": [
                    "[$Milestone_x0020_1_x0020_Due_x0020_Date]",
                    {
                      "operator": "+",
                      "operands": [
                        {
                          "operator": "*",
                          "operands": [
                            86400000,
                            5
                          ]
                        },
                        "@now"
                      ]
                    }
                  ]
                },
                "yellow",
                {
                  "operator": "?",
                  "operands": [
                    {
                      "operator": "<=",
                      "operands": [
                        "[$Milestone_x0020_1_x0020_Due_x0020_Date]",
                        "@now"
                      ]
                    },
                    "red",
                    "green"
                  ]
                }
              ]
            },
            {
              "operator": "==",
              "operands": [
                "[$Milestone_x0020_1_x0020_Status]",
                "Delayed"
              ]
            }
          ]
        }
      ]
    }
  },
  "txtContent": "@currentField"
}

This example applies the following formatting based on the Milestone 1 Status column:

• Complete: blue background color
• On Track and date in future: green background color
• On Track and date within 5 days: yellow background color
• On Track and date in past: red background color
• Delayed: yellow background color if date is in past, grey otherwise

Note that the internal names of the columns are used in the formatting. If your column names have spaces, replace them with _x0020_. For example, Milestone 1 Due Date becomes Milestone_x0020_1_x0020_Due_x0020_Date.