I have a list made that tracks several employee training dates. We are currently entering the date they last tested/trained. Trainings are due every 12 months or every 36 months.

How can I make the date changed to light red if not within the past 12 months and mint green if it was within the last 12 months?

Needs to be a rolling 12 months from todays date not a set date where I have to continuously change the timing.

1 Answer 1


You can use SharePoint JSON column formatting for your requirements. Use JSON like:

  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "font-weight": "bold",
    "padding-left": "10px",
    "color": "=if(Number(@currentField) != 0,'white','')",
    "background-color": "=if(Number(@currentField) != 0,if(@currentField < addDays(@now,-365),'#ff0000','#00a170'),'')"

You can find steps to apply JSON column formatting at: Use column formatting to customize SharePoint.

Here I have used Number(@currentField) != 0 to check if SharePoint date column is blank or not. You can find other ways to check if SharePoint date & time column is empty or not at: SharePoint JSON formatting: Check if date & time column is blank/empty.

You can also use internal name of date column in format like [$TrainingDate] in place of @currentField. You can get the internal name of your SharePoint list columns by following this article: How to find the Internal name of columns in SharePoint Online?


enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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