Aug 19 2022 06:32 AM - edited Aug 19 2022 06:37 AM
I have a calculated column in my SP list (Renewal Date), it looks at another column (Date Purchased) and adds 4 years to it.
The formula in the calculated column is below;
=TEXT(DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased])))
Now I would like to conditionally format the Renewal Date column, so that if the date is within 6 months of today, add a colour to the cell background, e.g. yellow. Or colour the text red/bold.
How can I achieve this?
Aug 19 2022 06:40 AM
@jonboylib so what have you tried so far?
Aug 19 2022 06:47 AM
@RobElliott Think i've figured it out using the code below. Converting 6 months to milliseconds - that correct?
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if(@currentField <= @now, '',if(@currentField <= @now + 15768000000 && @currentField > @now,'orange','green'))"
}
}
Aug 19 2022 06:49 AM
Aug 19 2022 08:24 AM
@RobElliott spoke too soon, doesn't seem to be working - see image.
I want any dates within the next coming month to be orange, the others can remain grey.
This is the code i'm using.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if(@currentField <= @now, 'grey',if(@currentField <= @now + 2629800000 && @currentField > @now,'orange','grey'))"
}
}
My calculated column contains the following formula;
=IF(Category="Desktop",DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased])),"false")