I would like to get some help developing a JSON code for Microsoft List with the following conditions:

The column is - Compliance Status

Condition 1: if column "Completion Status" is "Open" and column "Remaining Days" is between 5 to 30 then "Nearly Overdue"

Condition 2: if column "Completion Status" is "Open" and column "Remaining Days" is less than 5 then "Overdue"

  • Are you trying to create a calculated column in the list (not editable from list forms)? Or are you trying to customize the display text of "Completion Status" to show the Overdue/"Nearly Overdue text instead of "Open" based on "Remaining Days" column? Commented Dec 28, 2023 at 13:36

1 Answer 1


It sounds like you want a calculated column

=IF([Completion Status]="Open", IF(AND([Remaining Days]>=5, [Remaining Days]<=30), "Nearly Overdue", IF([Remaining Days]<5, "Overdue", "")), "")

This formula checks if "Completion Status" is "Open". If it is, it then checks if "Remaining Days" is between 5 and 30 (inclusive) and returns "Nearly Overdue". If "Remaining Days" is less than 5, it returns "Overdue". If "Completion Status" is not "Open", it returns an empty string.

If you're specifically looking for JSON:

  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "attributes": {
    "class": "=if([$Completion_x0020_Status] == 'Open' && [$Remaining_x0020_Days] >= 5 && [$Remaining_x0020_Days] <= 30, 'Nearly Overdue', if([$Completion_x0020_Status] == 'Open' && [$Remaining_x0020_Days] < 5, 'Overdue', ''))"

This JSON code checks the "Completion Status" and "Remaining Days" columns and sets the class of the "Compliance Status" column accordingly. Please replace Completion_x0020_Status and Remaining_x0020_Days with the internal names of your columns if they are different.

If you want to change the text of the "Compliance Status" column instead of the CSS class, you can modify the txtContent property instead of the class attribute.

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.