Forum Discussion

pnthrzrule's avatar
pnthrzrule
Iron Contributor
Apr 24, 2020
Solved

Conditional Formatting Files Older Than Certain number of days or years

Hello Everyone,   I'm aware of the new Conditional Formatting Rule builder that just dropped to my tenant last week. However, I'm wanting to implement formatting that will mark files that have a mo...
Document Library
files
Lists
SharePoint Online
sharepoint server
  • RobElliott's avatar
    RobElliott
    Apr 25, 2020

    Hi Michael, (pnthrzrule), yes we do this at our company. The JSON is below and subtracts 2 years (in milliseconds) from today's date. If the Modified date is older than that then the date is red, otherwise it's green:

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if([$Modified] <= @now-63113904000, '#c64444', '#468757')"
  }
}

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User

     

jeremyELAM's avatar
jeremyELAM
Copper Contributor
May 17, 2023

RobElliott Would I be able to flag items that have a date that is older than a week before today's date? For example, I have an item with today's date (5/17/2023). In one week from today, I want this item to be flagged, and I want this to happen for all items.

At them moment, the formatting that I have is "if the variable is before a fixed date," but I don't want the date to be fixed - I want it to always be a week before today's date, if that makes sense.

 

RobElliott's avatar
RobElliott
Silver Contributor
May 19, 2023

jeremyELAM as an alternative to using milliseconds you can use the recently-released addDays function. It gives the same result but is perhaps slightly easier to read.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField < addDays(@now,-7), '#830909', '#409f93')",
    "color": "white",
    "padding-left": "10px"
  }
}

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

Resources