Forum Discussion

SLCLARKE23's avatar
SLCLARKE23
Copper Contributor
Mar 25, 2022

Conditional Formatting in Sharepoint List based on expiry date within 30 days

I currently have a list which shows training. the box shades red when the training has expired and is green when it is active. I would like it to go Orange when the training is going to expire in the next 60 days. I was wondering if someone can help me write in the advance setting mode? Thank you

SharePoint Online
  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Mar 25, 2022

    SLCLARKE23 dates in JSON use milliseconds. So 60 days is 5184000000 milliseconds:

     

     

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

     

     

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

    • SOE_Tech's avatar
      SOE_Tech
      Brass Contributor
      Feb 21, 2023
      Can i be so bold and ask how to to "nothing" if the current field is empty?

      With the current JSON the empty fields get red as well
      • Rob_Elliott's avatar
        Rob_Elliott
        Bronze Contributor
        Feb 19, 2024

        SOE_Tech if the current field is empty then use =if(Number(@currentField)==0,'', if.....

         

        {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "font-weight": "bold",
    "background-color": "=if(Number(@currentField) == 0, '', if(@currentField <= addDays(@now, 30), 'red', if(@currentField > addDays(@now, 30) && @currentField < addDays(@now, 60), 'orange', '#006666')))",
    "color": "white"
  }
}

          

         

        Rob
        Los Gallardos
        Microsoft Power Automate Community Super User.
        Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

         

    • AmarTrivedi's avatar
      AmarTrivedi
      Copper Contributor
      Feb 19, 2024

      RobElliott This will work for particular field. But can you please help me with if we want it to be applied to whole view?

      • Rob_Elliott's avatar
        Rob_Elliott
        Bronze Contributor
        Feb 19, 2024

        AmarTrivedi format the view with JSON similar to this:

        {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalRowClass": "=if([$Arrival] < addDays(@now(),10), 'ms-bgColor-red', if([$Arrival] >= @now() && [$Arrival] <= addDays(@now(),30), 'ms-bgColor-yellow', 'ms-bgColor-green')) + ' ms-fontColor-white'"
}

         

         

        Rob
        Los Gallardos
        Microsoft Power Automate Community Super User.
        Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

         

Resources