I am trying to get something to work on SharePoint Online with JSON column formatting. What i have currently been able to achieve was, if a column is filled in ==> fill green if empty fill orange. I would like to convert this to a date-based check.

For example: When the column contains data = Fill it as green & show text in white

If todays date = or is less then the date in LaunchDate Column AND the field is empty ==> Fill it red

todays date equals or is more then 1 month before LaunchDate ==> Fill Yellow

1 month or less before launch date =:=> fill Orange.

I am struggeling with stringing this all together. Any ideas?

enter image description here

After applying solution suggested below i get as result(i emptied an expired field to illustrate: enter image description here

1 Answer 1


Here are the rules as I understand them:

  • Column has a value: Green with White text
  • Column has no value and LaunchDate is in the past: Red
  • Column has no value and LaunchDate is within 30 days: Orange
  • Column has no value and LaunchDate is more than 30 days out: Yellow

Assuming those are correct, this format should work. Just apply it to each column (column settings > format this column > advanced mode) you'd like it applied to and be sure LaunchDate remains part of the view.

  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField.displayValue",
  "attributes": {
    "class": "=if(length(@currentField), 'ms-bgColor-green ms-fontColor-white', if([$LaunchDate] <= @now, 'ms-bgColor-red', if([$LaunchDate] <= addDays(@now,30), 'ms-bgColor-orange', 'ms-bgColor-yellow')))"

enter image description here

I used the ms-bgColor-* classes since that was easier to map to your preferred colors. However, the sp-css-backgroundColor-* or sp-field-severity--* classes may be closer to what you're looking for.

  • Hi Chris, Thanks for your swift reply, but I seem to be either missing something or something seems to go wrong :) i applied the formatting as you specified and everything is showing red. Do note we use european date formate so dd/mm/yyyy Commented Aug 24, 2022 at 14:03
  • I made a tweak to the format to make it easier to read and add the schema. I also hadn't tested it, but now I have and added a screenshot. Are you sure LaunchDate is the internal name of your column? Commented Aug 24, 2022 at 15:21
  • Hi Chris, You are a SAINT8 Commented Aug 25, 2022 at 6:11

Your Answer

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

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