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

2024-11-07T20:07:47.4766667+00:00

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?

SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
3,100 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Xyza Xue_MSFT 26,051 Reputation points Microsoft Vendor
    2024-11-08T05:39:31.0266667+00:00

    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.


  2. 2024-11-08T12:24:24.2933333+00:00

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


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.