1

In SharePoint O365, in the modern experience, I have a list of training certificates that have an expiration date of one year from date taken.

I would like to use conditional formatting to create a status column that shows me the certificate is one of these:

  1. Current, green background - taken sometime within the last 10 months.

  2. Expiring, Orange background - Taken 11 or 12 months ago.

  3. Expired, Red background - date past one year ago

I have done this for another list using the status of the request of whether it was approved or rejected but when it comes to dates I have found it rather difficult to get an example even close to what I am looking for. I used the formatting wizard that is not built in but it only compares to todays date and I can't use the date of a field.

THE ANSWER:

picture of the working status JSON formatting

{
  "elmType": "div",
  "style": {
    "background-color": "=if(@currentField >= @now - 26297460000 ,'green', (if(@currentField > @now - 31556952000, '#F07440','red'))"
  },
  "children" :[
   {
   "elmType": "span",
   "txtContent" : "=if(@currentField >= @now - 26297460000 ,'Current', (if(@currentField > @now - 31556952000, 'Expiring','Expired'))",
   "style": {
    "color": "white"
   }
   }
  ]
}
2

The way I understood, you want to display text like 'expired', 'expiring','current' instead of date. Extending jerry's answer, you can use below

{
  "elmType": "div",
  "style": {
    "background-color": "=if(@currentField >= @now - 25920000000 ,'green', (if(@currentField >= @now - 31104000000, '#ffa59b','red'))"
  },
  "children" :[
   {
   "elmType": "span",
   "txtContent" : "=if(@currentField >= @now - 25920000000 ,'Current', (if(@currentField >= @now - 31104000000, 'Expiring','Expired'))",
   "style": {
    "color": "white"
   }
   }
  ]
}
  • Thanks! The colors and dates were a little off but I got it! This made a LOT more sense than what I was messing with! – user3161018 Jun 27 at 17:27
1

You can use JSON to do the work. use the JSON code below

{
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField >= @now - 25920000000 ,'green', (if(@currentField >= @now - 31104000000, '#ffa59b','red'))"
  }
}

Reference: http://thebaretta.blogspot.com/2018/08/sharepoint-online-conditional.html

  • THANKS! That example and source REALLY helped me understand a little bit more about JSON and dates with the conditional formatting!! – user3161018 Jun 27 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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