Unable to conditionally format a column using JSON format.

Question

Unable to conditionally format a column using JSON format.

Jonathan Kearney 20

I currently have a list in Microsoft Teams that has a column titled "12 Month Due Date". I would like to make the background of the cell turn red if the date entered is within the active month or past due. I do not understand how to utilize JSON format and have been unsuccessful googling options to teach myself.

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Hi @Jonathan Kearney

Thank you for reaching out to the community. You can accomplish this by applying JSON column formatting to the 12 Month Due Date column.

The following JSON will highlight the cell in red when the date falls within the current month or is from an earlier month. Blank cells and dates in future months will remain unchanged.

{
 "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
 "elmType": "div",
 "txtContent": "@currentField.displayValue",
 "style": {
 "width": "100%",
 "padding": "4px 8px",
 "box-sizing": "border-box",
 "background-color": "=if(Number(@currentField) == 0, 'transparent', if(getYear(@currentField) < getYear(@now) || (getYear(@currentField) == getYear(@now) && getMonth(@currentField) <= getMonth(@now)), '#d13438', 'transparent'))",
 "color": "=if(Number(@currentField) != 0 && (getYear(@currentField) < getYear(@now) || (getYear(@currentField) == getYear(@now) && getMonth(@currentField) <= getMonth(@now))), 'white', 'inherit')",
 "font-weight": "=if(Number(@currentField) != 0 && (getYear(@currentField) < getYear(@now) || (getYear(@currentField) == getYear(@now) && getMonth(@currentField) <= getMonth(@now))), '600', 'normal')"
 }
}

To apply it:

Open the list in Microsoft Teams.
Select the arrow next to 12 Month Due Date.
Select Column settings and then Format this column.
Select Advanced mode.
Paste the JSON and select Save.

Please note that this formatting highlights every date in the current calendar month, including dates later in the month, as well as dates from previous months.

I hope this helps. If the formatting does not work as expected, please verify that 12 Month Due Date is configured as a Date and Time column.