SOLVED

Formatting Dates

Copper Contributor

In my SharePoint list I have a column of review dates. I was wanting to create a formatting formula which, I have tried using Co-Pilot to various levels of success I want the standard DD-MM-YY in the following colours
In excess of 30 Days in the future - Black on White background
Less than 30 days into the future - Red on light orange/Yellow background
In the Past - Bold White on Red Background.

 

Any help would be appreciated

5 Replies
best response confirmed by nshaw75 (Copper Contributor)
Solution

@nshaw75 format your date column in advanced mode with the following JSON:

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

 

0-SP.png

 

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

Thanks very much for the help. :grinning_face:
How would I change the Date format to DD/MM/YYY

I seemed to get that bit totally messed up now too :crying_face:

@nshaw75 make sure your regional settings are set correctly in Site Settings.

Hi Rob, many thanks. That one was definitely a case of "I can't see the wood for the trees " :D
1 best response

Accepted Solutions
best response confirmed by nshaw75 (Copper Contributor)
Solution

@nshaw75 format your date column in advanced mode with the following JSON:

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

 

0-SP.png

 

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

View solution in original post