1

I'm using a SharePoint List to track annual training requirements. I would like for the column to display different colors based on the date they have entered into the field relative to when they need to accomplish the training again. So, what I'm trying to accomplish:

365 or more = red
<365 = green
304-364 (60 days till expiration) = yellow
Date is displayed in white regardless of above

Here's what I have so far (lifted from another answer and modified slightly):

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "background-color": "=if(@currentField >= @now - 31449600000 ,'green', (if(@currentField > @now - 31536000000, '#F07440','red'))"
  }
}
Improve this question
2
  • Could you define the requirement logic a little bit clearly? @currentField older than 365 days from current date is 'Red', between 304 and 364 is green. What is the logic for Yellow and White? Need to make sure the logic incorporate all days Commented Sep 6, 2024 at 19:58
  • I see where the confusion lies. Let me amend the conditions to be more clear: 366 or more = red 365-305 (expiring within 60 days) = yellow 0-304 = green Commented Sep 9, 2024 at 15:32

1 Answer 1

Reset to default
1

Here is what I can come up with. Of course, you could modify to suit your needs.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField <= (@now - (86400000 *365)), 'red', if((@currentField <=  (@now - (86400000 *304)) &&  @currentField >= (@now - (86400000 *364))), 'yellow', 'green'))",
    "font-size": "12px",
    "font-weight": "bold",
    "justify-content": "center",
    "color": "=if((@currentField <=  (@now - (86400000 *304))) &&  (@currentField >= (@now - (86400000 *364))), 'black', 'white')"
  }
}

enter image description here

Improve this answer
3
  • Thank you! This is exactly what I needed. Is there a way to gray out a cell based on the value of another cell? Example: If the record's Supervisor column = "Yes", "Training" = grayed out. If the record's Supervisor column = "No", "Training" = date formatting applied Commented Sep 10, 2024 at 21:48
  • Yes, that is possible. You can refer other column (not the current column being formatted) like [$DueDate] in the condition. For more info visit this reference link Commented Sep 11, 2024 at 12:22
  • Also mark this question as answered and post a new question with the detailed requirements Commented Sep 11, 2024 at 12:25

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.