Forum Discussion

Zeman_176's avatar
Zeman_176
Copper Contributor
Jun 23, 2023
Solved

A date in a sharepoint list is to be automatically coloured red after 2 weeks using conditional form

Hello all, I have created a list with MS Lists in Sharepoint. Our internal processes are recorded there. Employee XY creates a new line and enters his offer number in one column and the date on w...
  • Rob_Elliott's avatar
    Rob_Elliott
    Jun 26, 2023

    Zeman_176 if you're formatting a particular column, click the dropdown next to the the column header, select Column settings -> Format this column.

     

    Your JSON would be similar to the code below although not exactly the same as I haven't looked in detail at what you are trying to do:

     

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

     

     

     




SvenSieverding's avatar
SvenSieverding
Bronze Contributor
Jun 24, 2023

Hi Zeman_176 ,

you can format your view using SharePoint JSON list formatting (like in https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#formatting-an-item-when-a-date-column-is-before-or-after-todays-date-advanced )

If your columns are "OrderNumber" and "Date" then the json would look like this

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if([$OrderNumber]!='',if(Number([$Date])<(Number(@now)+(1000*60*60*24*14)),'sp-css-backgroundColor-BgCoral',''),'')"
}


Go to your view selector, select "Format current View" and then "Advanced Mode". Paste that json there and save.

 

Best Regards,
Sven



Zeman_176's avatar
Zeman_176
Copper Contributor
Jun 26, 2023

SvenSieverding 

Hello Sven, thank you very much for your effort.
The solution is very close to what I had in mind. Is it possible to set the conditional formatting only for a column and not for whole rows?

I tried to apply your code only to the column "Ang.date" and also adjusted the names of the variables, but this only resulted in all data in the column "Ang.date" no longer being displayed

 

 

I'm happy if you have any more ideas 🙂

  • Rob_Elliott's avatar
    Rob_Elliott
    Silver Contributor
    Jun 26, 2023

    Zeman_176 if you're formatting a particular column, click the dropdown next to the the column header, select Column settings -> Format this column.

     

    Your JSON would be similar to the code below although not exactly the same as I haven't looked in detail at what you are trying to do:

     

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

     

     

     




    • Zeman_176's avatar
      Zeman_176
      Copper Contributor
      Jun 27, 2023

      Rob_Elliott 

      thank you very much. Your tip helped me, I modified it a bit, see below, and it works great.

       

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

       

      Ich habe jedoch noch eine Frage, ich habe versucht eine zweite IF-Bedingung hinzuzufügen. Die zweite Bedingung sollte sich darauf beziehen, dass sich die Spalte sich nur rot färbt, wenn die Spalte "Auftrag" leer ist.

       

Resources