2

I am wanting to create a column called "Traffic Light" on an existing SharePoint Library.

Column Name = Traffic Light

Data Type = Single line of text

Formula:

="<DIV style='font-weight:bold; font-size:24px; color:"&IF([Review Status]="Approaching","Green",IF([Review Status]="OverDue,"Red","Yellow"))&";'>•</DIV>"

Despite my best endeavors I can't seem to get the correct syntax for this formula.

Help needed please.

1
  • Which version of SharePoint you are using? Jul 30 '19 at 10:54
1

Use below formula:

="<DIV style='font-weight:bold; font-size:24px; color:"&IF([Review Status]="Approaching","Green",IF([Review Status]="OverDue","Red","Yellow"))&";'>•</DIV>"

Note:

  1. Return this field as Number field.
  2. Microsoft blocked handling HTML markup in SharePoint calculated fields for recent versions, see here.
  3. If you are using SharePoint Online/2019 then you can use Column Formatting to achieve similar requirements.

Update:

You can add below JSON on the Review Status column(In Column settings-->Format this column) itself to show the colored column:

{
  "elmType": "div",
  "style": {
    "padding": "0 4px"
  },
  "attributes": {
    "class": {
      "operator": ":",
      "operands": [
        {
          "operator": "==",
          "operands": [
            {
              "operator": "toLowerCase",
              "operands": [
                "@currentField"
              ]
            },
            {
              "operator": "toLowerCase",
              "operands": [
                "Approaching"
              ]
            }
          ]
        },
        "sp-css-backgroundColor-successBackground50",
        {
          "operator": ":",
          "operands": [
            {
              "operator": "==",
              "operands": [
                {
                  "operator": "toLowerCase",
                  "operands": [
                    "@currentField"
                  ]
                },
                {
                  "operator": "toLowerCase",
                  "operands": [
                    "OverDue"
                  ]
                }
              ]
            },
            "sp-css-backgroundColor-errorBackground50",
            {
              "operator": ":",
              "operands": [
                {
                  "operator": "==",
                  "operands": [
                    {
                      "operator": "toLowerCase",
                      "operands": [
                        "@currentField"
                      ]
                    },
                    {
                      "operator": "toLowerCase",
                      "operands": [
                        "In Progress"
                      ]
                    }
                  ]
                },
                "sp-css-backgroundColor-warningBackground50",
                {
                  "operator": ":",
                  "operands": [
                    {
                      "operator": "==",
                      "operands": [
                        {
                          "operator": "toLowerCase",
                          "operands": [
                            "@currentField"
                          ]
                        },
                        {
                          "operator": "toLowerCase",
                          "operands": [
                            ""
                          ]
                        }
                      ]
                    },
                    "sp-css-backgroundColor-blueBackground07",
                    ""
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  },
  "txtContent": "@currentField"
}

enter image description here

Creating another column and Referencing Review Status:

Use below code:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=if([$Review_x0020_Status] == '', '', '•')",
    "style": {
        "color": "=if([$Review_x0020_Status] == 'Not Due', 'green', if([$Review_x0020_Status] == 'OverDue', 'red', if([$Review_x0020_Status] == 'Approaching','yellow','white')))",
        "font-size": "5em"
    }
}

Note: Use internal name of Review Status column, How to check Column Internal Name for a SharePoint List?

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.