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.