conditionnal formating on a column based on a seperate look up column

Question

I have a column called ETA in my SharePoint list, and I would like to apply conditional formatting based on the value of another column. The other column is a lookup column called Permits_x003a_Permit_x0020_Expiry_x0020_Date, which pulls data from a date field.

What I want is for the ETA column to change colors based on the date in the Permits_x003a_Permit_x0020_Expiry_x0020_Date column:

If the date is less than 60 days away, I want ETA to be RED.
If the date is between 61 and 90 days away, I want ETA to be YELLOW.
If the date is more than 90 days away, I want ETA to be GREEN.

However, when I try to apply these conditions, the Permits_x003a_Permit_x0020_Expiry_x0020_Date column is not appearing in the list of available choices. Could this be because it's a lookup column?

Answer

Hi @Senneville, Danielle (Horne-CCR - CA) ,

Yes, lookup columns cannot be displayed in conditionnal formating.

To achieve the desired conditional formatting, you can use the following JSON formatting example for the ETA column:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "[$Permits_x003a_Permit_x0020_Expir.lookupValue]",
  "style": {
    "background-color": "=if(Date([$Permits_x003a_Permit_x0020_Expir.lookupValue]) - @now <= 5184000000, '#ff0000', if((Date([$Permits_x003a_Permit_x0020_Expir.lookupValue]) - @now <= 7776000000) && (Date([$Permits_x003a_Permit_x0020_Expir.lookupValue]) - @now >= 5184000000), '#ffff00', '#00ff00'))"
  }
}

Note: Make sure you are using correct internal name of your extended lookup date column.

Result:

User's image

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

There seems to be something not right happing. All my cells are red with no more date value.

Share via

conditionnal formating on a column based on a seperate look up column

2 answers

Your answer