SOLVED

Conditional formatting for dates based on another cell value

Copper Contributor

Hi, I am trying to add conditional formatting to my sharepoint list but cant figure out the answer for my scenario. 

 

I want to change the rows to orange if the column 'Status' shows "Awaiting Payment" and todays date is between 30 and 60 days after the date in the 'Date bill sent to client' column. 

 

I then want to change the rows to red if todays date is 60 days or more after the date in the 'Date bill sent to client' column and the 'Status' is still "Awaiting payment".

 

Any help much appreciated :)

3 Replies
best response confirmed by DWOps (Copper Contributor)
Solution

@DWOps Use this JSON for your list view formatting

 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if([$Status] == 'Awaiting Payment', if(@now >= addDays([$DateBillSent],60),'sp-field-severity--severeWarning',if(@now > addDays([$DateBillSent],30) && @now < addDays([$DateBillSent],60),'sp-field-severity--warning50','')), '')"
}

 

 

Where Status and DateBillSent are internal names of your status and date columns. You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online? 

 

Output

ganeshsanap_0-1691393359357.png


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.

@ganeshsanap you're a star! perfect, thank you.

@DWOps You are welcome, I am glad it worked for you. 


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.

1 best response

Accepted Solutions
best response confirmed by DWOps (Copper Contributor)
Solution

@DWOps Use this JSON for your list view formatting

 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if([$Status] == 'Awaiting Payment', if(@now >= addDays([$DateBillSent],60),'sp-field-severity--severeWarning',if(@now > addDays([$DateBillSent],30) && @now < addDays([$DateBillSent],60),'sp-field-severity--warning50','')), '')"
}

 

 

Where Status and DateBillSent are internal names of your status and date columns. You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online? 

 

Output

ganeshsanap_0-1691393359357.png


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.

View solution in original post