Forum Discussion

Mr_Jordy's avatar
Mr_Jordy
Copper Contributor
Aug 30, 2023

Traffic Light Highlight for Date Training Completed Column

Hello-

 I need to create a stoplight for the below column in a modern SharePoint list functioning off of the date input (see below). Training date last for 365 days/1 year. I would like it to highlight as follows:

- Day 1-334: Green

- Day 335-365 (within 30 days): Yellow

- 365+ (overdue): Red

 

Below is snapshot of the column as well as JSON:

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"box-sizing": "border-box",
"padding": "0 2px",
"overflow": "hidden",
"text-overflow": "ellipsis"
},
"attributes": {
"class": "sp-css-backgroundColor-BgLightGray sp-field-fontSizeSmall sp-css-color-LightGrayFont"
},
"txtContent": "[$DateTrainingCompleted.displayValue]"
}

 

SharePoint Online

1 Reply

  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Aug 31, 2023

    Mr_Jordy Try using JSON like below for your date column formatting: 

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "[$DateTrainingCompleted.displayValue]",
  "style": {
    "background-color": "=if(Number([$DateTrainingCompleted]) == 0 , '', if([$DateTrainingCompleted] < addDays(@now, -365), 'red', if([$DateTrainingCompleted] < addDays(@now, -335), 'yellow', if([$DateTrainingCompleted] <= @now, 'green', ''))))",
    "padding-left": "14px",
    "color": "=if(Number([$DateTrainingCompleted]) == 0, '', 'white')"
  }
}

     

    Where DateTrainingCompleted is the internal name of your column. You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?

     

    You can also use the @currentField instead of [$DateTrainingCompleted] in the JSON.

     

    Reference: SharePoint JSON formatting: Check if date & time column is blank/empty 

     

    Output: 

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Resources