1

I have a Microsoft List (SharePoint List) and have a date field. I want to have a conditional format of the color based on today, i.e. if the date is in the past, grey, if the date is between today and a month from now, Amber and if the date is more than a month in the future, green.

I understand I will need JSON but I haven't used that before.

Has anyone done this before and has the JSON for it?

1 Answer 1

0

You have to use the JSON column formatting for this. Use below JSON for your date column:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "background-color": "=if(Number(@currentField) == 0, '', if(@currentField < @now, '#808080', if(@currentField >= @now && @currentField <= addDays(@now, 30), '#FFBF00', if(@currentField > addDays(@now, 30), '#00FF00', ''))))"
  },
  "children": [
    {
      "elmType": "span",
      "txtContent": "@currentField",
      "style": {
        "color": "black"
      }
    }
  ]
}

Output:

enter image description here

You can apply additional formatting as per your requirements. If you want to change color of text, use color property instead of background-color in above JSON.

For more information, follow Microsoft documentation: Use column formatting to customize SharePoint


Update from comments:

You can check if date column is empty or not using Number() operator. Use below expression for background-color for your requirements:

=if(Number(@currentField) == 0, '', if(@currentField < @now, '#808080', if(@currentField >= @now && @currentField <= addDays(@now, 30), '#FFBF00', if(@currentField > addDays(@now, 30), '#00FF00', ''))))

Source: SharePoint JSON formatting: Check if date & time column is blank/empty

5
  • I gave it a try and it worked perfectly on my date field, however I also have a calculated date field, and on that one I got no background colours. Any ideas ? Also is there anywhere I can find the codes for the colours :-) Thinking of a lighter grey colour. Nov 2, 2022 at 12:27
  • Forgot to mention that some dates are blank and they are coming out grey - can they be no background colour ? Nov 2, 2022 at 12:29
  • Check update answer for blank dates. You can google search for HEX codes for any colors like: google.com/search?q=hex+for+gray+color+code Nov 2, 2022 at 12:42
  • Can you ask new question for calculate date field issue? You can include additional information in new question like formula used for calculated column, return type set in column settings, etc. Nov 2, 2022 at 12:43
  • Hi @PaulDennis, did you try this? Is it working for you? Nov 3, 2022 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.