Forum Discussion

jonboylib's avatar
jonboylib
Iron Contributor
Aug 19, 2022

SP List Calculated Column Items Expiring Soon

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...
Lists
SharePoint Online
  • jonboylib's avatar
    jonboylib
    Iron Contributor
    Aug 19, 2022

    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'))"
  }
}

     

    • RobElliott's avatar
      RobElliott
      Silver Contributor
      Aug 19, 2022
      Yes, date comparisons are always in milliseconds in JSON. Good stuff.
      • jonboylib's avatar
        jonboylib
        Iron Contributor
        Aug 19, 2022

        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.  

         

         

        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")

Resources