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?
Solved! Go to 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,
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,
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.
User | Count |
---|---|
196 | |
122 | |
88 | |
48 | |
41 |
User | Count |
---|---|
285 | |
163 | |
138 | |
77 | |
73 |