Forum Discussion

Alison50's avatar
Alison50
Copper Contributor
Dec 13, 2022
Solved

Conditional formatting in MS Lists

Hi

 

I'm trying to set up conditional formatting on a MS List column with dates. We are trying to display the following:

* green formatting when the date has passed

* red formatting on the actual date

* amber formatting when the date is coming up in the next 7 days

* no formatting for dates beyond the next 7 days or for blank dates

 

So far, I have the following but it isn't working:

 

{
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(Number(@currentField) == 0, '', if(@currentField >= @now + 604800000, '', if(@currentField >= @now + 86400000, '#feebc0', if(@currentField <= @now, '#cbf1cc', if(@currentField = @now, '#fabbc3', '')))))"
}
}
microsoft lists
  • ganeshsanap's avatar
    ganeshsanap
    Dec 13, 2022

    Alison50 Use below JSON for date column formatting: 

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(Number(@currentField) == 0, '', if(toLocaleDateString(@currentField) < toLocaleDateString(@now), 'green', if(toLocaleDateString(@currentField) == toLocaleDateString(@now), 'red', if(@currentField > @now && @currentField <= addDays(@now, 7), 'orange', ''))))"
  }
}

     

    Related read: SharePoint JSON formatting: Check if date & time column is blank/empty - different ways to check if date column is blank or 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.

  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Dec 13, 2022

    Alison50 Use below JSON for date column formatting: 

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(Number(@currentField) == 0, '', if(toLocaleDateString(@currentField) < toLocaleDateString(@now), 'green', if(toLocaleDateString(@currentField) == toLocaleDateString(@now), 'red', if(@currentField > @now && @currentField <= addDays(@now, 7), 'orange', ''))))"
  }
}

     

    Related read: SharePoint JSON formatting: Check if date & time column is blank/empty - different ways to check if date column is blank or 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