SOLVED

Need help with formating colors based on time in columns

Brass Contributor

Hello!

 

Need some help with formating columns based on what time they got, if its more or less, then I want it to change color. See pictures.

 

Sorry for my bad english, hope you guys can help me anyway :)

15min question.pngresponsetime question.png

18 Replies

HIi @288088!

 

What are the field types for 15min and Reponse time?

@Matti Paukkonen Hello! Those columns are "Calculated" Values

 

15min has formula:

=TEXT([Assigned technicians]-[case received];"h:mm:ss")

 

Response time has formula:

=TEXT(Started-[case received];"h:mm:ss")

@288088

I think it's not possible to do a date/time comparison to a field containing only time value. 

You can use those date/time fields (case received, assigned technicians and started) for time comparison and apply column formattings to 15min and response time fields.

@288088 

 

Here is an example for 15min column, just replace the field references with your fields and add $schema reference in the beginning (editor messes up the links :( )

 

 

 

{
    
    "elmType": "div",
    "attributes": {
      "class":  {"operator":"?",
        "operands":[
            {"operator":"<","operands":[
                "[$assigned]",
                {
                   "operator": "+",
                   "operands": [
                      "[$case]",
                      900000
                   ]
                }]},"sp-field-severity--good",""

        ]}
    },
     "children": [
      {
        "elmType": "span",
        "style": {
          "display": "inline-block",
          "padding": "0 4px"
        },
        "attributes": {
          "iconName": {"operator":"?",
            "operands":[
                {"operator":"<","operands":[
                    "[$assigned]",
                    {
                       "operator": "+",
                       "operands": [
                          "[$case]",
                          900000
                       ]
                    }]},"CheckMark",""
    
            ]}
        }
      },
      {
        "elmType": "span",
        "txtContent": "@currentField"
      }
    ]
  }

 

 

 

@Matti Paukkonen 

 

Thanks, I managed to get the color and checkmark, it looks good. But is there anyway to make <0:15:00 it will become green with a checkmark and when >0:15:00 it will become red with a warning symbol?overunder15min.png

@288088, just modify formatting a little bit.

 

For background color change this:

"sp-field-severity--good",
""

to

"sp-field-severity--good",
"sp-field-severity--blocked"

 

And for icon

"CheckMark",
""

to

"CheckMark",
"ErrorBadge"

 

Those out-of-the-box css classes and icons can be found here: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#apply-co...

@Matti Paukkonen 

I'm sorry but i'm really new to this, what do you mean by "just  modify formatting a little bit" I cant get it to become red or green depending if the minutes are above or below 0:15:00 :( What line should I format?

@288088 

Here is a complete example

 

{
    
    "elmType": "div",
    "attributes": {
      "class":  {"operator":"?",
        "operands":[
            {"operator":"<","operands":[
                "[$assigned]",
                {
                   "operator": "+",
                   "operands": [
                      "[$case]",
                      900000
                   ]
                }]},"sp-field-severity--good","sp-field-severity--blocked"

        ]}
    },
     "children": [
      {
        "elmType": "span",
        "style": {
          "display": "inline-block",
          "padding": "0 4px"
        },
        "attributes": {
          "iconName": {"operator":"?",
            "operands":[
                {"operator":"<","operands":[
                    "[$assigned]",
                    {
                       "operator": "+",
                       "operands": [
                          "[$case]",
                          900000
                       ]
                    }]},"CheckMark","ErrorBadge"
    
            ]}
        }
      },
      {
        "elmType": "span",
        "txtContent": "@currentField"
      }
    ]
  }

 

@Matti Paukkonen 

 

I copied your code and pasted inte the format for the "15min" column and changed 

"[$case]",

to

"[case received]",

and

"[$assigned]",

to

"[Assigned technicians]",

but it still doesnt work :( I feel like i'm misunstanding something?

 

15minnotred.png

 

{
  "$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json</a>",
  "debugMode": true,
  "elmType": "div",
  "attributes": {
    "class": {
      "operator": "?",
      "operands": [
        {
          "operator": "<=",
          "operands": [
            "[Assigned technicians]",

            {
              "operator": "+",
              "operands": [
                "[case received]",
                900000
              ]
            }
          ]
        },
        "sp-field-severity--good",
        ""
      ]
    }
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": {
          "operator": "?",
          "operands": [
            {
              "operator": "<=",
              "operands": [
                "[Assigned technicians]",
                {
                  "operator": "+",
                  "operands": [
                    "[case received]",
                    900000
                  ]
                }
              ]
            },
            "CheckMark",
            ""
          ]
        }
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

 

You need to use field internal names and reference fields like [$fieldName].

@Matti Paukkonen 

 

So I found the fieldname for the column "15min", it is "Inom_x0020_15_x0020_minuter".

 

So I pasted it into the fields with "[$Inom_x0020_15_x0020_minuter]" but it still doesnt work.

 

What does the "900000" Stand for? It is 0:15:00 minutes?

 

 

{
  "$schema": "<a href="<a href="https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json</a>" target="_blank"><a href="https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json</a" target="_blank">https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json</a</a>>",
  "debugMode": true,
  "elmType": "div",
  "attributes": {
    "class": {
      "operator": "?",
      "operands": [
        {
          "operator": "<",
          "operands": [
            "[$Inom_x0020_15_x0020_minuter]",
            {
              "operator": "+",
              "operands": [
                "[$Inom_x0020_15_x0020_minuter]",
                900000
              ]
            }
          ]
        },
        "sp-field-severity--good",
        "sp-field-severity--blocked"
      ]
    }
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": {
          "operator": "?",
          "operands": [
            {
              "operator": "<",
              "operands": [
                "[$Inom_x0020_15_x0020_minuter]",
                {
                  "operator": "+",
                  "operands": [
                    "[$Inom_x0020_15_x0020_minuter]",
                    900000
                  ]
                }
              ]
            },
            "CheckMark",
            "ErrorBadge"
          ]
        }
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

 

@288088, you need to replace $case in my example with internal name of case received and $assigned with internal name of Assigned technicians. Sorry, I didn't clarify that.

 

Value 900000 is 15 minutes, it's calculated like 15 x 60 x 1000.

Okey! So I did exacly this but it still doesnt work. It wont change color depending in value in 15min column =(

@288088 This section of your code is incorrect (the part that makes it go red): 

"attributes": {
        "iconName": {
          "operator": "?",
          "operands": [
            {
              "operator": "<",
              "operands": [
                "[$Inom_x0020_15_x0020_minuter]",
                {
                  "operator": "+",
                  "operands": [
                    "[$Inom_x0020_15_x0020_minuter]",
                    900000
                  ]
                }
              ]
            },
            "CheckMark",
            "ErrorBadge"
          ]
        }
      }

 

This: "operator": "<"


Needs to be this: "operator": ">"

Also... 
The first section of code (the part that makes it go green) should be less than or equal to 15 minutes otherwise if they hit 15 minutes exactly it won't be green or red. 

So the part that makes it go green should have this: "operator": "<="

T. 

@288088, you can save the formatting without errors? 

For me, my example works correctly:

clipboard_image_1.png

Can you send me your version as an attachment? I can check it out.

 

@Matti Paukkonen 

 

Yes I can save the format without errors. My columns "ärende inkommet" and "Ärende tilldelat" is just text based column and i've copy pasted the date and time from another program because I need seconds in it too.

redandgreen15min.JPG

 

This is the code I tried from @tonyknibb 

{
  "$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>",
 "debugMode": true,
  "elmType": "div",
  "attributes": {
    "class": {
      "operator": "?",
      "operands": [
        {
          "operator": ">",
          "operands": [
            "[$_x00c4_rende_x0020_tilldelat]",
            {
              "operator": "+",
              "operands": [
                "[$_x00c4_rende_x0020_inkommet]",
                900000
              ]
            }
          ]
        },
        "sp-field-severity--good",
        "sp-field-severity--blocked"
      ]
    }
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": {
          "operator": "?",
          "operands": [
            {
              "operator": "<=",
              "operands": [
                "[$_x00c4_rende_x0020_tilldelat]",
                {
                  "operator": "+",
                  "operands": [
                    "[$_x00c4_rende_x0020_inkommet]",
                    900000
                  ]
                }
              ]
            },
            "CheckMark",
            "ErrorBadge"
          ]
        }
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

 

 

@Matti Paukkonen 

 

This is what it looks like with your code:

clipboard_image_1.png

_x00c4_rende_x0020_tilldelat = Ärende tilldelat

_x00c4_rende_x0020_inkommet = Ärende inkommet

{
  "$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>",
  "debugMode": true,
  "elmType": "div",
  "attributes": {
    "class": {
      "operator": "?",
      "operands": [
        {
          "operator": "<",
          "operands": [
            "[$_x00c4_rende_x0020_tilldelat]",
            {
              "operator": "+",
              "operands": [
                "[$_x00c4_rende_x0020_inkommet]",
                900000
              ]
            }
          ]
        },
        "sp-field-severity--good",
        "sp-field-severity--blocked"
      ]
    }
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": {
          "operator": "?",
          "operands": [
            {
              "operator": "<",
              "operands": [
                "[$_x00c4_rende_x0020_tilldelat]",
                {
                  "operator": "+",
                  "operands": [
                    "[$_x00c4_rende_x0020_inkommet]",
                    900000
                  ]
                }
              ]
            },
            "CheckMark",
            "ErrorBadge"
          ]
        }
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

 

 

best response confirmed by 288088 (Brass Contributor)
Solution

@Matti Paukkonen 

 

I managed with another code to get the result that I wanted, but thanks for the effort!

 

{
  "$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>",
  "elmType": "div",
  "attributes": {
    "class": "=if(@currentField <= '02:00:00', 'sp-field-severity--good', if(@currentField >= '02:00:00', 'sp-field-severity--blocked', '"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "=if(@currentField <= '02:00:00', 'CheckMark', if(@currentField >= '02:00:00', 'Warning', '"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

 

1 best response

Accepted Solutions
best response confirmed by 288088 (Brass Contributor)
Solution

@Matti Paukkonen 

 

I managed with another code to get the result that I wanted, but thanks for the effort!

 

{
  "$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>",
  "elmType": "div",
  "attributes": {
    "class": "=if(@currentField <= '02:00:00', 'sp-field-severity--good', if(@currentField >= '02:00:00', 'sp-field-severity--blocked', '"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "=if(@currentField <= '02:00:00', 'CheckMark', if(@currentField >= '02:00:00', 'Warning', '"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

 

View solution in original post