Add a column for traffic light for a Due Date

%3CLINGO-SUB%20id%3D%22lingo-sub-2356333%22%20slang%3D%22en-US%22%3EAdd%20a%20column%20for%20traffic%20light%20for%20a%20Due%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2356333%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22MessageEditor%22%3E%3CDIV%20class%3D%22lia-js-block-events%22%3E%3CDIV%20class%3D%22lia-form-row%20lia-form-body-entry%22%3E%3CDIV%20class%3D%22lia-quilt-row%20lia-quilt-row-standard%22%3E%3CDIV%20class%3D%22lia-quilt-column%20lia-quilt-column-24%20lia-quilt-column-single%22%3E%3CDIV%20class%3D%22lia-quilt-column-alley%20lia-quilt-column-alley-single%22%3E%3CDIV%20class%3D%22lia-form-input-wrapper%22%3E%3CDIV%20class%3D%22message-body-editor%22%3E%3CDIV%20class%3D%22lia-mentions-hints%22%3E%3CDIV%20class%3D%22lia-panel%20lia-panel-standard%22%3E%3CDIV%20class%3D%22lia-decoration-border%22%3E%3CDIV%20class%3D%22lia-decoration-border-content%22%3E%3CDIV%20class%3D%22lia-panel-content-wrapper%22%3E%3CDIV%20class%3D%22lia-panel-content%22%3EHello%20-%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-decoration-border-bottom%22%3E%3CDIV%3E%3CSPAN%3EI%20need%20to%20create%20a%20traffic%20light%20or%20colored%20dot%20for%20a%20Modern%20SharePoint%20list%20of%20policies%20that%20need%20to%20be%20updated%20each%20year.%26nbsp%3B%20The%20list%2C%20below%2C%20shows%20a%20Due%20Date%20for%20each%20item%20on%20the%20list.%26nbsp%3B%20The%20Due%20Date%20is%20a%20calculated%20field%20based%20on%20the%20Last%20Date%20updated%20plus%20one%20year.%26nbsp%3B%20The%20traffic%20light%20or%20color%20dot%20will%20be%20in%20the%20column%20called%20Status%20and%20will%20indicate%20the%20following%20based%20on%20today's%20date%3A%20%40now.%26nbsp%3B%20Does%20anyone%20have%20the%20JSON%20code%20for%20this%3F%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3ERed%20%E2%80%93%20Today%20is%20past%20the%20Due%20Date%3C%2FP%3E%3CP%3EAmber%20%E2%80%93%20Today%20is%20fewer%20than%203%20days%20before%20the%20Due%20Date%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EYellow%E2%80%93%20Today%20is%20fewer%20than%2010%20days%20before%20the%20Due%20Date%26nbsp%3B%3C%2FP%3E%3CP%3EGreen%E2%80%93%26nbsp%3B%20Today%20is%20greater%20than%20or%20equal%20to%2010%20days%20before%20the%20Due%20Date%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20in%20my%20snip%2C%20all%20rows%20are%20showing%20a%20red%20dot%2C%20even%20though%20there%20is%20more%20than%2010%20days%20before%20the%20due%20date.%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2356333%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360637%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20column%20for%20traffic%20light%20for%20a%20Due%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360637%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1052246%22%20target%3D%22_blank%22%3E%40RebeccaBB%3C%2FA%3E%2C%20you%20can%20do%20this%20with%20the%20following%20JSON%20(based%20on%20your%20criteria).%20Time%20comparisons%20in%20JSON%20are%20done%20in%20milliseconds.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%7B%0A%20%20%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fcolumn-formatting.schema.json%22%2C%0A%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%22txtContent%22%3A%20%22%40currentField%22%2C%0A%20%20%22style%22%3A%20%7B%0A%20%20%20%20%22background-color%22%3A%20%22%3Dif(%5B%24Due%5D%20%26lt%3B%20%40now%2C%20'%23780909'%2C%20(if(%5B%24Due%5D%20%26gt%3B%3D%20%40now%20%26amp%3B%26amp%3B%20%5B%24Due%5D%20%26lt%3B%3D%20%40now%2B259200000%2C%20%20'%23e74c3c'%2C%20(if(%5B%24Due%5D%20%26gt%3B%3D%20%40now%20%26amp%3B%26amp%3B%20%5B%24Due%5D%20%26lt%3B%3D%20%40now%2B864000000%2C%20%20'%23f39c12'%2C%20'%2398a148')))%22%2C%0A%20%22width%22%3A%20%2220px%22%2C%0A%20%22border-radius%22%3A%20%2225px%22%0A%20%20%7D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EWhich%20gives%20the%20following%20result%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22dueDate.png%22%20style%3D%22width%3A%20501px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281049i932C83282CACC529%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22dueDate.png%22%20alt%3D%22dueDate.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%2C%20Website%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
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!

2 Replies

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:

dueDate.png

 

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

 

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?