Forum Discussion

nshaw75's avatar
nshaw75
Brass Contributor
Jun 10, 2024

Formatting Dates

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

  • 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"
      }
    }

     

     

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

  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor

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

     

     

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

    • nshaw75's avatar
      nshaw75
      Brass Contributor
      How would I change the Date format to DD/MM/YYY

      I seemed to get that bit totally messed up now too 😢
    • nshaw75's avatar
      nshaw75
      Brass Contributor
      Thanks very much for the help. 😀

Resources