0

I am trying to create a Traffic Light system within SharePoint Online (Office 365) using JSON.

The dates should run in accordance to said, Traffic light signal.

What I'm trying to do:

If "Todays Date" is GREATER THAN the [End Date] = RED LIGHT

If "Todays Date" is approaching (or is equal to [3 DAYS away from]) the [END DATE] = YELLOW LIGHT

If "Todays Date" is GREATER than 3 DAYS or more away from [END DATE] = GREEN LIGHT

I have read multiple posts for potential solutions. But haven't found any success. The JSON code below is what I currently have. From what I can tell the last snippet of the code runs successfully if([$RD] == 'Approaching','','purple')))" as well as everything else. However the remainder of the IF statements aren't (=if([$RD] == 'No', 'green', if([$RD] == 'Yes', 'red',.

(RD) Remaining Days Calculated Formula:

=TODAY()>[End Date]

Format Reference Column

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
      "elmType": "div",
      "txtContent": "=if([$RD] == '', '', '•')",
      "style": {
        "color": "=if([$RD] == 'No', 'green', if([$RD] == 'Yes', 'red', if([$RD] == 'Approaching','','purple')))",
        "font-size": "5em"
      }
    }

As a visual aid this is what my Sharepoint List currently looks like. As a visual aid this is what my Sharepoint List currently looks like.

2

Updated to show Traffic Light as circle in the output:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "•",
  "style": {
    "color": "=if([$EndDate]>(@now +259200000), 'Green', if([$EndDate]>@now && [$EndDate]<= (@now +259200000), 'Yellow', 'Red'))",
    "font-size": "5em",
    "justify-content": "center"
  }
}

enter image description here


You can try this:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if([$EndDate]>(@now +259200000), 'GREEN LIGHT', if([$EndDate]>@now && [$EndDate]<= (@now +259200000), 'YELLOW LIGHT', 'RED LIGHT'))",
  "style": {
    "background-color": "=if([$EndDate]>(@now +259200000), 'Green', if([$EndDate]>@now && [$EndDate]<= (@now +259200000), 'Yellow', 'Red'))",
    "color": "black",
    "font-size": "1.5em",
    "justify-content": "center"
  }
}

enter image description here

Note: In the formula, 86400000 X 3 =259200000

Please refer to MS Article for Column Formatting

1
  • This solution works. Thank you! I feel a bit stupid. I should've realized the equation should've been 86400000 X 3 =259200000 instead of just "3" (which I thought automatically converted into days).
    – Cherarium
    Dec 23 '20 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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