Forum Discussion
Sharepoint List - Combining conditions for color formating
Hello everyone,
i created a list in Sharepoint for some invoices with three dates:
- Checked
- Booked
- Paid
I would now like to have the colored condition:
If the date in booked is older then 6 days AND there is no date in "Paid" -> mark "CurrentField" red
If the date in booked is younger then 3 days AND there is no date in "Paid" -> mark "CurrentField" green
If the date in booked is between 3-6 days AND there is no date in "Paid" -> mark "CurrentField" orange
I am struggeling with the "AND" condition alot as whatever i do the background either turns red/green or the date in the column just vanishes.
If someone could help me with that id appreciate it.
This is how far i have come with the code..
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(Number(@currentField) != 0, '', if(Number(@currentField) <= addDays(@now, -3) && Number(@currentField) > addDays(@now, -6) && Number([$Paid]) == 0, 'orange', if(Number(@currentField) <= addDays(@now, -6) && Number([$Paid]) == 0, 'red', 'green')))",
"padding-left": "14px",
"color": "=if(Number(@currentField) != 0 && Number(@currentField) <= addDays(@now, -3) && (Number([$Paid]) == '' || Number([$Paid]) == 0), 'white', '')"
}
}
SOE_Tech Try using this JSON for Booked column:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "background-color": "=if(Number(@currentField) == 0 || Number([$Paid]) != 0, '', if(@currentField <= addDays(@now, -6),'red', if(@currentField <= addDays(@now, -3),'orange', 'green')))", "padding-left": "14px", "color": "=if(Number(@currentField) == 0 || Number([$Paid]) != 0, '', 'white')" } }
Let me know if this is what you want or if you want to change any conditions in above JSON.
Make sure you are using correct internal name of your Paid column. You can get/confirm internal name of your date column by following the article: How to find the Internal name of columns in SharePoint Online?
You can find different ways to check if SharePoint date column is blank or empty using JSON formatting at: SharePoint JSON formatting: Check if date & time column is blank/empty
Output:
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.