Conditional Formatting in Microsoft Lists

Gaby Gorgol 20 Reputation points
2023-07-28T15:35:06.7833333+00:00

Hello,

I am trying to have two date columns within Microsoft Lists highlight based on the following:

Column 1 (Last DOT inspection date). Example date: 7/1/2022 would highlight red

if date is < 365 days RED

if date is < 335 days YELLOW

if date is < 305 days GREEN

Column 2 (Last BIT inspection date). Example date: 5/1/2022 would highlight red.

if date is < 90 days RED

if date is < 60 days YELLOW

if date is < 30 days GREEN

Thinking I'm going to have to use JSON but I'm not very familiar with that.

Thank you!

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
11,173 questions
0 comments No comments
{count} votes

Accepted answer
  1. Emily Du-MSFT 49,846 Reputation points Microsoft Vendor
    2023-07-31T07:28:36.1666667+00:00

    Here are steps:

    1.Click "Last DOT inspection date" column -> Column settings -> Format this column -> Advanced mode.

    2.Using following codes.

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType": "div",
      "txtContent": "@currentField",
      "style": {
        "background-color": "=if([$LastDOTinspectiondate] < addDays(@now,-365), 'red', if([$LastDOTinspectiondate] < addDays(@now,-335), 'yellow','green'))"
      }
    }
    

    3.Click "Last DOT inspection date" column -> Column settings -> Format this column -> Advanced mode.

    4.Using following codes.

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType": "div",
      "txtContent": "@currentField",
      "style": {
        "background-color": "=if([$LastBITinspectiondate] < addDays(@now,-90), 'red', if([$LastBITinspectiondate] < addDays(@now,-60), 'yellow','green'))"
      }
    }
    

    5.Result:

    User's image


    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Gérald Döserich 765 Reputation points
    2023-07-28T20:29:32.51+00:00

    You can use Excel-style expression syntax. See: https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#formatting-items-based-on-arbitrary-dates-advanced

    E.g:

    {
       "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
       "elmType": "div",
       "txtContent": "@currentField",
       "style": {
          "color": "=if([$DueDate] <= @now + 86400000, '#ff0000', '')"
       }
    }
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.