SP List Calculated Column Items Expiring Soon

Iron Contributor

I have a calculated column in my SP list (Renewal Date), it looks at another column (Date Purchased) and adds 4 years to it.

 

The formula in the calculated column is below;

 

 

=TEXT(DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased])))

 

 

Now I would like to conditionally format the Renewal Date column, so that if the date is within 6 months of today, add a colour to the cell background, e.g. yellow. Or colour the text red/bold.

 

How can I achieve this?

4 Replies

@jonboylib so what have you tried so far?

@RobElliott Think i've figured it out using the code below. Converting 6 months to milliseconds - that correct?

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if(@currentField <= @now, '',if(@currentField <= @now + 15768000000 && @currentField > @now,'orange','green'))"
  }
}

 

Yes, date comparisons are always in milliseconds in JSON. Good stuff.

@RobElliott spoke too soon, doesn't seem to be working - see image.

 

I want any dates within the next coming month to be orange, the others can remain grey.  

 

d1.png

 

This is the code i'm using.

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if(@currentField <= @now, 'grey',if(@currentField <= @now + 2629800000 && @currentField > @now,'orange','grey'))"
  }
}

 My calculated column contains the following formula;

 

=IF(Category="Desktop",DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased])),"false")