Forum Discussion

Zeman_176's avatar
Zeman_176
Copper Contributor
Jun 23, 2023

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 which the offer was sent in another column.

To highlight open offers, the date entered in the date column should automatically turn red after 2 weeks. However, this is only possible if the column "Order" is empty.


I could only find the following information about my request in the community

https://learn.microsoft.com/de-de/sharepoint/dev/declarative-customization/column-formatting#formatting-an-item-when-a-date-column-is-before-or-after-todays-date-advanced


Unfortunately, this does not provide me with a satisfactory solution.

I would be happy to receive suggestions for a solution.

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

         

         

         




Resources