I've reviewed a lot of threads and none seem to quite fit my fact pattern.

I'm using the Modern MS Lists, in Office 365 Online.

We have a list of contracts that we want to color code rows based on approaching due dates, based off our Date/Time field named CurrentCompletionDate (it is a date only field). Our goal is to ensure we're not getting renewal requests at the last minute, so past due isn't as big of an issue as others have had.

My ideal solution would also be to only apply color coding if our Status field does not equal Archived. (We have a choice field with Active, Terminated, and Archived as choices).

No color: CurrentCompletionDate is more than 120 days out

Green: CurrentCompletionDate is between 120 and 60 days out

Yellow: CurrentCompletionDate is between 60 and 30 days out

Red: CurrentCompletionDate is less than 30 days out (or has past).

I know that you need to use milliseconds to account for the days:

120 days = 10368000000; 60 days = 5184000000; 30 days = 2592000000

But I'm stumped as to how to get the rows to color based on those three conditions. I assume you enter the json code after clicking Format Current View and then Conditional Formatting and then Advanced Mode. I do not know what the code should be for those rulesets and to see if it can first compare to the Status field. If not, we could always create a second view that filters out the Archived, and go from there.

Thank you in advance for any assistance anyone can offer.

1 Answer 1


You should be able to use the following JSON in Advanced Mode to accomplish what you want:

  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalRowClass": "=if([$Status]=='Archived', '', if(addDays(@now,30) > [$CurrentCompletionDate], 'ms-bgColor-red', if(addDays(@now,60) > [$CurrentCompletionDate], 'ms-bgColor-yellow', if(addDays(@now,120) > [$CurrentCompletionDate], 'ms-bgColor-green', ''))))"

I used variations of the ms-bgColor-* classes to apply the colors. You can find additional ones here: https://pnp.github.io/List-Formatting/groupings/classes/ms-bgColor/

Alternatively, you could use the sp-css-backgroundColor-* classes. Details here: https://pnp.github.io/List-Formatting/groupings/classes/sp-css-backgroundColor/

  • Thank you so much!! And thank you for the other color palettes!
    – FSomers1
    Aug 28, 2022 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.