Forum Discussion

RebeccaBB's avatar
RebeccaBB
Copper Contributor
May 14, 2021

Add a column for traffic light for a Due Date

Hello - 
I need to create a traffic light or colored dot for a Modern SharePoint list of policies that need to be updated each year.  The list, below, shows a Due Date for each item on the list.  The Due Date is a calculated field based on the Last Date updated plus one year.  The traffic light or color dot will be in the column called Status and will indicate the following based on today's date: @now.  Does anyone have the JSON code for this?  

Red – Today is past the Due Date

Amber – Today is fewer than 3 days before the Due Date  

Yellow– Today is fewer than 10 days before the Due Date 

Green–  Today is greater than or equal to 10 days before the Due Date 

 

As you can see in my snip, all rows are showing a red dot, even though there is more than 10 days before the due date.  Thank you!

SharePoint Online
  • RobElliott's avatar
    RobElliott
    Silver Contributor
    May 17, 2021

    Hi RebeccaBB, you can do this with the following JSON (based on your criteria). Time comparisons in JSON are done in milliseconds.

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$Due] < @now, '#780909', (if([$Due] >= @now && [$Due] <= @now+259200000,  '#e74c3c', (if([$Due] >= @now && [$Due] <= @now+864000000,  '#f39c12', '#98a148')))",
	"width": "20px",
	"border-radius": "25px"
  }
}

    Which gives the following result:

     

    Rob
    Los Gallardos
    Intranet, SharePoint, Website and Power Platform Manager (and classic 1967 Morris Traveller driver)

     

    • RebeccaBB's avatar
      RebeccaBB
      Copper Contributor
      May 21, 2021
      Hi Rob,
      Thank you for providing this to me. I copied it into the formula field and all the items on my list are still showing as Red/ Overdue. I tried changing to Classic view, but that did not correct the error. I also changed the field called Due Date from being a calculated field to be one where the user has to manually enter the due date. This worked! But when I closed the folder and reopened it, everything was back to showing as Red/ Overdue. Do you have any insight on why this is not working?

Resources