SOLVED

Quick JSON formula question regarding date plus 365

Brass Contributor

I am trying to format a column to change color if TODAY is greater than the date entered, plus 365 days. It's basically to show a color for expiration of date entered. Below is what I was trying to figure out, but this doesn't work. Thanks!

 

 

{
  "schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
   "additionalRowClass": "=if(@now()+365 > [$COLUMN_x0020_NAME], 'sp-field-severity--severeWarning', '')"
}

 

 

 

 

 

 

 

10 Replies
best response confirmed by alandarr (Brass Contributor)
Solution

@alandarr 

 

Is this what you are looking for?

Annotation 2020-04-13 093346.png

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if([$DateColumn] >= @now + 365, 'ms-fontColor-themePrimary ms-fontWeight-bold', '')"
}
}

 

I hope this helps.

 

Norm

Yes that works! I was originally wanting the background to change, but changing the text works great. Many thanks for your help!

Hi @alandarr, glad it worked.

 

Norm

@Norman Young Taking another look at that, it still isn't working correctly. I'm trying to color it if it is more than 365 days old from today, i.e. more than a year old.

 

I've tried to move the formula around, but I still can't get it to do that. Not sure what's going on LOL!

Share your code and what is the column name (internal)?

@Norman Young The code works, but your example date is in the future. What I am trying to do is color the text when the date is past one year old. The attached example (screenshot) is your code with a date that is over 365 days in the past. The text is unchanged. It seems simple enough, but I've struggled with it for a couple days before you took a crack at it. Makes me crazy!

 

Screen Shot 2020-04-14 at 7.57.13 AM.png

 

If you move the items around, a date that is less than a year old still shows as colored and bold. It's only supposed to do that if it's more than a year old.

 

 

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if(@now >= [$Expiration] + 365, 'ms-fontColor-themePrimary ms-fontWeight-bold', '')"
}
}

 

 

 

@Norman Young The code works, but your example date is in the future. What I am trying to do is color the text when the date is past one year old. The attached example (screenshot) is your code with a date that is over 365 days in the past. The text is unchanged. It seems simple enough, but I've struggled with it for a couple days before you took a crack at it. Makes me crazy!

 

Screen Shot 2020-04-14 at 7.57.13 AM.png

 

If you move the items around, a date that is less than a year old still shows as colored and bold. It's only supposed to do that if it's more than a year old.

 

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if(@now >= [$Expiration] + 365, 'ms-fontColor-themePrimary ms-fontWeight-bold', '')"
}
}

 

 

Hi @alandarr,

 

Try this:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if(floor((Number(@now)-Number([$DateColumn]))/(1000*60*60*24*365)) > 0, 'ms-fontColor-themePrimary ms-fontWeight-bold', '')"
}
}

Annotation 2020-04-14 154757.png 

@Norman Young That works! I knew it had to be more complicated than what I was trying. Can I buy you a cup of coffee? Thank you so very much with helping. I'm new to Sharepoint and feel a bit overwhelmed. Thanks again!

 

Now, if I can figure out how to mark this answer as correct instead of the other one...

I appreciate the coffee offer @alandarr but it is unnecessary. I'm glad things worked out. Reach out again if you get stuck.

 

Norm

1 best response

Accepted Solutions
best response confirmed by alandarr (Brass Contributor)
Solution

@alandarr 

 

Is this what you are looking for?

Annotation 2020-04-13 093346.png

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if([$DateColumn] >= @now + 365, 'ms-fontColor-themePrimary ms-fontWeight-bold', '')"
}
}

 

I hope this helps.

 

Norm

View solution in original post