Forum Discussion

SOE_Tech's avatar
SOE_Tech
Brass Contributor
Aug 03, 2023

Color Coding Sharepoint list (conditional formating)

Hello everyone,

i created a list in Sharepoint for some invoices with three dates:

  • Checked
  • Booked
  • Paid

I would now like to have the colored condition:

If the date in checked is older then 3 days AND there is no date in "Booked" -> mark "checked" red

 

If someone could help me with that id appreciate it.

 

This is how far i have come with the code.. that works but

 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField <= @now - 518400000, 'red', if(@currentField <= @now - 259200000, 'orange', 'green'))",
    "padding-left": "14px"
  }
}

 

SharePoint Online

5 Replies

  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor
    Aug 03, 2023

    SOE_Tech  the following JSON works. You don't need to use the milliseconds as JSON now accepts addDays and to check for an empty date column use Number([$Booked]) == 0

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField <= addDays(@now, -3) && Number([$Booked]) == 0, 'red', '')",
    "padding-left": "14px",
    "color": "=if(@currentField <= addDays(@now, -3) && Number([$Booked]) == 0, 'white', '')"
  }
}

     

     

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.
    Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

    • SOE_Tech's avatar
      SOE_Tech
      Brass Contributor
      Aug 04, 2023

      Rob_Elliott 

       

      Hello Rob

      The code works really well for the "Checked" column. I now wanted to add it to the 2nd column and check if "Paid" has a date in it.

       

      Only changing "Booked" to "Paid" will make the background red if the "Booked" column is empty and no background if the "Booked" column has a date in it.

       

      I am a bit confused as to how and why that behavior is that way

      • ganeshsanap's avatar
        ganeshsanap
        MVP
        Aug 04, 2023

        SOE_Tech Try something like this: 

         

        {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(Number(@currentField) != 0 && @currentField <= addDays(@now, -3) && Number([$Paid]) == 0, 'red', '')",
    "padding-left": "14px",
    "color": "=if(Number(@currentField) != 0 && @currentField <= addDays(@now, -3) && Number([$Paid]) == 0, 'white', '')"
  }
}

         

        Make sure you are using correct internal name of your Paid column. You can get/confirm internal name of your date column by following the article: How to find the Internal name of columns in SharePoint Online? 

         

        You can find different ways to check if SharePoint date column is blank or empty using JSON formatting at: SharePoint JSON formatting: Check if date & time column is blank/empty 

        Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Resources