Forum Discussion

ChiefKeefe's avatar
ChiefKeefe
Copper Contributor
May 07, 2020
Solved

Adding color in column validation

I've created a custom list in SharePoint online to track our department training. I have a date and time column titled "Expiration" that will list the date that the training/certification will expire...
  • RobElliott's avatar
    RobElliott
    May 07, 2020

    ChiefKeefe if you wanted to make it stand out a bit more then you could apply the formatting with a background color:

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$NextReview] <= @now + 604800000, '#d9184b', if([$NextReview] > @now+604800000 && [$NextReview] <= @now + 2592000000, '#ff9a00','#dddeee')",
    "color": "white",
    "padding-left": "10px"
  }
}

     

    which gives the following result:

     

     

    Rob
    Los Gallardos

    Microsoft Power Automate Community Super User

ChiefKeefe's avatar
ChiefKeefe
Copper Contributor
May 07, 2020

Alireza Rahimifarid Thank you. I've already read those two articles prior to reaching out to the community. The part I'm having issues with is integrating a formula. As I stated in my post, I would like to add a formula to this column that will turn orange once it's 30 days from expiration and then turn red once it's 7 days from expiration. 

  • RobElliott's avatar
    RobElliott
    Silver Contributor
    May 07, 2020

    Alireza Rahimifarid I don't think that will give ChiefKeefe the correct result, partly because it doesn't address the second part of the question about the expiration in the next 30 days, but also because the date comparison needs to be done with milliseconds. So 7 days in the future is @now + 604800000 and 30 days is @now + 2592000000.

     

    So Linda my solution is as follows and my column is called NextReview:

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if([$NextReview] <= @now + 604800000, '#d9184b', if([$NextReview] > @now+604800000 && [$NextReview] <= @now + 2592000000, '#ff9a00','#585153')"
  }
}

     

     

    which gives the following result:

     

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User 

Resources