If calculated value column exceeds 24 hours

%3CLINGO-SUB%20id%3D%22lingo-sub-998724%22%20slang%3D%22en-US%22%3EIf%20calculated%20value%20column%20exceeds%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-998724%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20this%20calculated%20column%20that%20shows%20values%20of%20%22hh%3Amm%3Ass%22.%20But%20i've%20noticed%20that%20if%20its%20exceeds%2024%3A00%3A00%20it%20will%20restarts%20to%20ex.%2001%3A00%3A00..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anywat%20to%20make%20a%20validation%20or%20format%20the%20column%20so%20when%20it%20exceeds%20ex.%2023%3A59%3A59%20it%20will%20show%20the%20text%20%22Over%2024%26gt%3B%20hours%22%20or%20something%3F%20Or%20is%20there%20any%20way%20to%20make%20it%20exceed%2024%3A00%3A00%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20code%20in%20the%20calculated%20column%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%7B%0A%20%20%22%24schema%22%3A%20%22%3CA%20href%3D%22%26lt%3Ba%20href%3D%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%3C%2FA%3E%22%20target%3D%22_blank%22%26gt%3B%3CA%20href%3D%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%26lt%3B%2Fa%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%3C%2FA%3E%26gt%3B%22%2C%0A%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%22attributes%22%3A%20%7B%0A%20%20%20%20%22class%22%3A%20%22%3Dif(%40currentField%20%26lt%3B%3D%20'02%3A00%3A00'%2C%20'sp-css-backgroundColor-successBackground50'%2C%20if(%40currentField%20%26gt%3B%3D%20'02%3A00%3A00'%2C%20'sp-css-backgroundColor-errorBackground50'%2C%20'%22%0A%20%20%7D%2C%0A%20%20%22children%22%3A%20%5B%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%22elmType%22%3A%20%22span%22%2C%0A%20%20%20%20%20%20%22style%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22display%22%3A%20%22inline-block%22%2C%0A%20%20%20%20%20%20%20%20%22padding%22%3A%20%220%204px%22%0A%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%22attributes%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22iconName%22%3A%20%22%3Dif(%40currentField%20%26lt%3B%3D%20'02%3A00%3A00'%2C%20'CheckMark'%2C%20if(%40currentField%20%26gt%3B%3D%20'02%3A00%3A00'%2C%20'Warning'%2C%20'%22%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%2C%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%22elmType%22%3A%20%22span%22%2C%0A%20%20%20%20%20%20%22txtContent%22%3A%20%22%40currentField%22%0A%20%20%20%20%7D%0A%20%20%5D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-998724%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-998938%22%20slang%3D%22en-US%22%3ERe%3A%20If%20calculated%20value%20column%20exceeds%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-998938%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F446256%22%20target%3D%22_blank%22%3E%40288088%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20try%20this%20in%20the%20txtcontent%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%40currentField%20%26gt%3B%2024%20%3F%20'Over%2024%20hours'%20%3A%20%40currentField%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-999187%22%20slang%3D%22en-US%22%3ERe%3A%20If%20calculated%20value%20column%20exceeds%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-999187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453843%22%20target%3D%22_blank%22%3E%40O365Developer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20kinda%20new%20to%20this%2C%20where%20in%20my%20code%20should%20I%20input%20this%20code%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20like%20example%20below%2C%20doesnt%20work.%20Guess%20its%20all%20wrong%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%7B%0A%20%20%22%24schema%22%3A%20%22%3CA%20href%3D%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%3C%2FA%3E%22%2C%0A%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%22attributes%22%3A%20%7B%0A%20%20%20%20%22class%22%3A%20%22%3Dif(%40currentField%20%26lt%3B%3D%20'02%3A00%3A00'%2C%20'sp-css-backgroundColor-successBackground50'%2C%20if(%40currentField%20%26gt%3B%3D%20'02%3A00%3A00'%2C%20'sp-css-backgroundColor-errorBackground50'%2C%20'%22%0A%20%20%7D%2C%0A%20%20%22children%22%3A%20%5B%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%22elmType%22%3A%20%22span%22%2C%0A%20%20%20%20%20%20%22style%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22display%22%3A%20%22inline-block%22%2C%0A%20%20%20%20%20%20%20%20%22padding%22%3A%20%220%204px%22%0A%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%22attributes%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22iconName%22%3A%20%22%3Dif(%40currentField%20%26lt%3B%3D%20'02%3A00%3A00'%2C%20'CheckMark'%2C%20if(%40currentField%20%26gt%3B%3D%20'02%3A00%3A00'%2C%20'Warning'%2C%20'%22%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%2C%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%22elmType%22%3A%20%22span%22%2C%0A%20%20%20%20%20%20%22txtContent%22%3A%20%3D%40currentField%20%26gt%3B%2024%20%3F%20'Over%2024%20hours'%20%3A%20%40currentField%0A%20%20%20%20%7D%0A%20%20%5D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1001111%22%20slang%3D%22en-US%22%3ERe%3A%20If%20calculated%20value%20column%20exceeds%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1001111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F446256%22%20target%3D%22_blank%22%3E%40288088%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20try%20enclosing%20them%20in%20double%20quotes%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1001285%22%20slang%3D%22en-US%22%3ERe%3A%20If%20calculated%20value%20column%20exceeds%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1001285%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453843%22%20target%3D%22_blank%22%3E%40O365Developer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20type%20like%20this%20I%20get%20error%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%20%7B%0A%20%20%20%20%20%20%22elmType%22%3A%20%22span%22%2C%0A%20%20%20%20%20%20%22txtContent%22%3A%20%3D%40currentField%20%26gt%3B%2024%20%3F%20%22Over%2024%20hours%22%20%3A%20%40currentField%0A%20%20%20%20%7D%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1003750%22%20slang%3D%22en-US%22%3ERe%3A%20If%20calculated%20value%20column%20exceeds%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1003750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453843%22%20target%3D%22_blank%22%3E%40O365Developer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20pls%20try%20below%20and%20post%20me%20the%20error%20message%20if%20you%20get%20any%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%22txtContent%22%3A%20%3D%22%40currentField%20%26gt%3B%2024%20%3F%20'Over%2024%20hours'%20%3A%20%40currentField%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1005227%22%20slang%3D%22en-US%22%3ERe%3A%20If%20calculated%20value%20column%20exceeds%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1005227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453843%22%20target%3D%22_blank%22%3E%40O365Developer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20doesnt%20work...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnly%20shows%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20getting%20%22Enter%20a%20valid%20JSON%20column%20formatting%20code%22%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%22children%22%3A%20%5B%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%22elmType%22%3A%20%22span%22%2C%0A%20%20%20%20%20%20%22txtContent%22%3A%20%3D%22%40currentField%20%26gt%3B%2024%20%3F%20'Over%2024%20hours'%20%3A%20%40currentField%22%2C%0A%20%20%20%20%20%20%22attributes%22%3A%20%7B%7D%2C%0A%20%20%20%20%20%20%22style%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22color%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%22operator%22%3A%20%22%3F%22%2C%0A%20%20%20%20%20%20%20%20%20%20%22operands%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22operator%22%3A%20%22%3D%3D%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22operands%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22%40currentField%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2200%3A00%3A00%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1007292%22%20slang%3D%22en-US%22%3ERe%3A%20If%20calculated%20value%20column%20exceeds%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1007292%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F446256%22%20target%3D%22_blank%22%3E%40288088%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EThats%20a%20mistake%20in%20typo%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%22%3Dif(%5B%24TimeField%5D%3D%3D10%2C'Over%2024'%2C'less%20than%2024')%22%20%2F%2Fassuming%20the%20TimeField%20is%20number%20or%20you%20can%20use%26nbsp%3B%40currentField%3D'24%3A00%3A00'%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ENot%20sure%2Cif%20you%20are%20aware%20about%20this%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FSharePoint%2Fsp-dev-solutions%2Fblob%2Fmaster%2Fsolutions%2FColumnFormatter%2Fdocs%2Fdocumentation%2Fdocs%2Fgetting-started.md%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FSharePoint%2Fsp-dev-solutions%2Fblob%2Fmaster%2Fsolutions%2FColumnFormatter%2Fdocs%2Fdocumentation%2Fdocs%2Fgetting-started.md%3C%2FA%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%26nbsp%3Byou%20can%20try%20with%20this%20in%20your%20workspace.%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Contributor

Hi!

 

I got this calculated column that shows values of "hh:mm:ss". But i've noticed that if its exceeds 24:00:00 it will restarts to ex. 01:00:00..

 

Is there anywat to make a validation or format the column so when it exceeds ex. 23:59:59 it will show the text "Over 24> hours" or something? Or is there any way to make it exceed 24:00:00?

 

This is my code in the calculated column:

 

{
  "$schema": "<a href="<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>" target="_blank"><a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a</a>>",
  "elmType": "div",
  "attributes": {
    "class": "=if(@currentField <= '02:00:00', 'sp-css-backgroundColor-successBackground50', if(@currentField >= '02:00:00', 'sp-css-backgroundColor-errorBackground50', '"
  },
  "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"
    }
  ]
}

 

7 Replies

@288088 

 

You can try this in the txtcontent

=@currentField > 24 ? 'Over 24 hours' : @currentField

 

@O365Developer 

 

I'm kinda new to this, where in my code should I input this code?

 

Did like example below, doesnt work. Guess its all wrong

 

{
  "$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-css-backgroundColor-successBackground50', if(@currentField >= '02:00:00', 'sp-css-backgroundColor-errorBackground50', '"
  },
  "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 > 24 ? 'Over 24 hours' : @currentField
    }
  ]
}

 

@288088 

Can you please try enclosing them in double quotes?

@O365Developer 

 

If I type like this I get error:

 {
      "elmType": "span",
      "txtContent": =@currentField > 24 ? "Over 24 hours" : @currentField
    }

@O365Developer 

 

can you pls try below and post me the error message if you get any

 

"txtContent": ="@currentField > 24 ? 'Over 24 hours' : @currentField"

@O365Developer 

 

It doesnt work...

 

Only shows like this:

 

I'm getting "Enter a valid JSON column formatting code" error.

 

"children": [
    {
      "elmType": "span",
      "txtContent": ="@currentField > 24 ? 'Over 24 hours' : @currentField",
      "attributes": {},
      "style": {
        "color": {
          "operator": "?",
          "operands": [
            {
              "operator": "==",
              "operands": [
                "@currentField",
                "00:00:00"

 

@288088 

 

Hi,

Thats a mistake in typo

 

"=if([$TimeField]==10,'Over 24','less than 24')" //assuming the TimeField is number or you can use @currentField='24:00:00'
 
Not sure,if you are aware about this
 you can try with this in your workspace.