0

In my list I have a column with dates. I want a field in this column to turn yellow when the date is more than X years ago. Where X is a number in another column.

For example: Date in field A2 is "1-1-2024". Number in field B2 is "2" (meaning a check is needed every two years). Because 1-1-2024 is less than two years ago, the field remains white. Date in field A3 is "1-1-2024". Number in field B3 is "1". Because 1-1-2024 is more than one year ago, the field turns yellow.

I'm fairly new to formatting in lists but have managed to get some things working. After a deep dive in online JSON tips I tried adding a multiplication to a working simpler JSON. It does something, but not what I am trying to do.

Is what I want possible? Do I need an additional column with a calculated number to make it work?

The code I tried:

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "= @currentField", "attributes": { "class": "=if (@currentField + 31536000000 * @currentItem.testnummer <= @now,'ms-bgColor-yellow', '')"

+++ EDIT 3 +++ Both samples in the answer of @matiur-rahman eventually worked! They didn't at first, but a week later suddenly the background-colour was there.

+++ EDIT 1 +++ A working JSON, but simpler, does change the background colour. This misses the multiplication with the numbers in the other column.

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "= @currentField", "attributes": { "class": "=if (@currentField + 31536000000 <= @now,'ms-bgColor-yellow', '')" }

}

+++ EDIT 2 +++ Two users kindly reacted to my initial question. I tried their suggestions. @Rothrock suggested suggested changing the direction of "<" and next the position of the arithmic. Both do work in the JSON that worked, but eacht time I insert the multiplication it doesn't. The fields remain yellow and the text disappears for some reason.

@matiur-rahman suggested a different approach. I tried that first in my existing List but there only the first test sample step worked, the sample that should do what I wanted did not. I made a new List and used the data, column names and samples that matiur gave me. The results were the same, as you can see here: result test prtscr of JSON DateColumn prtscr of JOSN DateXYearAgo

It should give the same results as @matiur-rahman's answer. The samples:

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "attributes": { "class": "=if(@currentField<Date(toLocaleDateString(Date(toString(getMonth(@now)) + '/'+ toString(getDate(@now))+ '/' + toString(getYear(@now)-[$NumberColumn])))), 'ms-bgColor-yellow', '')" } } { "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "=toLocaleDateString(Date(toString(getMonth(@now)) + '/'+ toString(getDate(@now))+ '/' + toString(getYear(@now)-[$NumberColumn])))", "style": { "color": "green", "font-size": "1em", "justify-content": "center" } }
Improve this question
4
  • This looks like it does the opposite of what you want, it colors it yellow until two years after the current date. If you change the <= around to >= I think that should work. It won't take into account leap years because there are a different number of ticks in a leap year. Commented Feb 18 at 15:30
  • @Rothrock Thank you for your swift answer. Unfortunately your suggestion did not work. One difference: In my code all dates went invisible. With the => the dates stayed black. But no yellow. If I remove "* @currentItem.testnummer" in my code, the fields turn yellow correctly (but without multiplied years). Thanks for the leap year warning. Good to know. Is not of importance in this list. Commented Feb 19 at 13:01
  • Might want to turn it around. All the other examples I've seen have the arithmetic on the other side of the comparator. @ currentField >= @ now - 604800000 Commented Feb 19 at 17:04
  • Thank you! Tried this first with the working code (without the mulitplication). That works fine. But unfortunately not with the multiplication in place. It seems there's something wrong with how I formulate this. Commented Feb 25 at 13:04

1 Answer 1

Reset to default
0

With a different approach. See the sample JSON and Screenshot below.

JSON for [DateColumn]

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "attributes": { "class": "=if(@currentField<Date(toLocaleDateString(Date(toString(getMonth(@now)) + '/'+ toString(getDate(@now))+ '/' + toString(getYear(@now)-[$NumberColumn])))), 'ms-bgColor-yellow', '')" } }

JSON for [DateXYearAgo]

It is showing a date X years ago from Today

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "=toLocaleDateString(Date(toString(getMonth(@now)) + '/'+ toString(getDate(@now))+ '/' + toString(getYear(@now)-[$NumberColumn])))", "style": { "color": "green", "font-size": "1em", "justify-content": "center" } }

enter image description here

Updated JSON for [DateColumn] using [style] element, but outcome is the same in my testing

In my testing, the outcome is the same as before when used CSS amber color code

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "background-color": "=if(@currentField<Date(toLocaleDateString(Date(toString(getMonth(@now)) + '/'+ toString(getDate(@now))+ '/' + toString(getYear(@now)-[$NumberColumn])))), '#FFBF00', '')" } }
Improve this answer
8
  • Thank you very much for your suggestion! Unfortunately it did not yet work. I first tested your sample for DateXYearAgo, where I changed the column name. That worked like in your screenshot (even though the month is the month before now for some reason). That was promising! Next I tried the sample JSON for the date column. I can see the logic in that: if the date in the field is less than the date that "code for DateXYearAgo" produces, make the background yellow. But the fields remain white. Strange, because your screenshot proves it worked when you did it. Commented Feb 25 at 13:31
  • You save the JSON applied to [DateColumn] and then refresh the view. Sometimes the view doesn't get refreshed automatically. Also, make sure you provide the correct internal name of the column when referring other columns in JSON. As you can see, I tested the JSON before posting it, so there must be something wrong with column names. You can try creating a new list with the same name of the columns that I've in the sample screenshot and try again Commented Feb 25 at 13:44
  • Thanks. I started a new list and used your column names and data sampled. Next I used your JSON samples. The sample for [DateXYearAgo] worked. The sample for [DateColumn] did not, unfortunately. I checked the internal column names to be sure. I saved both JSON fields and refreshed. All fields stay white. (I can't add screenshots here to show what I did, shall I edit my question to add information and screenshots?) Commented Feb 25 at 15:07
  • Yes, post whatever you can. I will try to use your JSON and see if it works for my test. Also, we can try another element like [style] instead of [attributes] to update back-ground color conditionally. Commented Feb 25 at 15:10
  • thanks! I added information in the question, hopefully you can use that to see what happens. Commented Feb 25 at 16:22

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.