2

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" } } ] }
Improve this question

2 Answers 2

Reset to default
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" } } ] }
Improve this answer
1
  • 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! Commented Jun 27, 2019 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

Improve this answer
1
  • THANKS! That example and source REALLY helped me understand a little bit more about JSON and dates with the conditional formatting!! Commented Jun 27, 2019 at 17:28

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.