How do I format my choices column to pick between 3 options when comparing it to my dates column in Microsoft Lists?

Question

I need a JSON conditional formatting for my Scorecard Status column that is set with choices. I want that column to reflect red, yellow, or green statuses based on the Due Dates column.

If it is the day of or after the date in Due Dates , the Scorecard Status column should show OVERDUE in a red box.

If it is 30 days or less before the date in Due Dates , the Scorecard Status column should show ALMOST LATE in a yellow box.

If it is more than 30 days before the date in Due Dates , the Scorecard Status column should show ON TIME in a green box.

I also want to be able to manually change it to COMPLETED or NO ACTION REQUIRED as needed. I've used the below code in an attempt to get just the first 3 boxes (the red, yellow, green), but it only shows red even though there are dates that should be green or yellow. Screenshot

{

  "$schema": https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json,

  "elmType": "div",

  "style": {

    "background-color": "=if([$Cutoff_x0020_Dates] < @now, '#d9534f', if([$Cutoff_x0020_Dates] <= @now + (30 * 86400000), '#f0ad4e', '#5cb85c'))",

    "color": "black",

    "padding": "15px",

    "border-radius": "auto"

  },

  "txtContent": "=if([$Cutoff_x0020_Dates] < @now, 'OVERDUE', if([$Cutoff_x0020_Dates] <= @now + (30 * 86400000), 'ALMOST LATE', 'ON TIME'))"

}

Any assistance would be greatly appreciated as I am new to this and for the life of me could not figure out what I am doing wrong.

Thank you,

Moderator's note: Moved from Microsoft 365 and Office | Other

Answer 1

Dear @Alexis,

Based on your description:

If it is the day of or after the date in Due Dates , the Scorecard Status column should show OVERDUE in a red box. If it is 30 days or less before the date in Due Dates , the Scorecard Status column should show ALMOST LATE in a yellow box. If it is more than 30 days before the date in Due Dates , the Scorecard Status column should show ON TIME in a green box.

This conditions seem to be conflicts, kindly clarify once again and provide me an example in the three condition so that I can assist you more efficiently.

I have create a scenario and the process worked to change the color based on due dates. However, the conditions are incorrect. Here is an image for it.

I am looking forward to hearing from you.