Sharepoint list JSON column with two conditional

[najwakaa]

I have two column in sharepoint list. 

Column A is Date & Column B is text

I would like to create a logic where the row will be highlighted to red if Column A is past today date AND Column B is blank. 

I found a code that will help to highlight column A if column B is blank. 

Refer to the picture, and even the future date (the one I circled) is still highlighted. 

However, I could not figure out how add the code to not highlight the date if the due date is not past yet. 

Can anyone help me?

p/s: if possible, how do I color the whole row red instead only the date cell? 

[v-xida-msft · Accepted solution]

Hi @najwakaa ,

Could you please share a bit more about your scenario?

Do you want to highlight the SP List Row to Red if Column A is past today date AND Column B is blank within your SP list itself?

Further, could you please share a bit more about the formula you used to highlight? Do you use SharePoint column formatting formula to achieve your needs?

Based on the needs that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Please take a try with the following workaround:Under Column Settings, choose Format this column, then click "Advanced mode", type the following formula:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
            "background-color": "=if([$Response]=='' && [$Due_x0020_Date] < @now, '#ff0000', '')"
  }
}

On your side, you should type the following formula:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$Response] == '' && [$Date] < @now, '#ff0000', '')"
  }
}

In addition, the SharePoint Column formatting formula only apply to column level rather than row level. If you want to highlight the row to Red if Column A is past today date AND Column B is blank, I afraid that there is no direct way to achieve your needs.

As an alternative solution, you could consider take a try to put above formula within the Column Format Box of all columns in your SP list.

More details about the SharePoint column format formula, please check the following article:

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

Best regards,

[najwakaa]

Thank you. It works. I didn't know that && can work on JSON. I was using the nested if and always get an error. I found that I was using the wrong field name.