1

I'm trying to do a conditional format on a SP column that gets its entries from PowerApps, but formatting the regular way only gives me the option to format by date, and not by time. I'm new to JSON and despite checking online I'm not really sure where to start.

What I wanted was the condition to colour the column(for the specific entry) based on the time. For example: condition is set to 9:30 and any entry after that time will be "flagged" red.

Code in question:

{

    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column- formatting.schema.json",
    "elmType": "div",
    "style": {
      "box-sizing": "border-box",
      "padding": "0 2px",
      "overflow": "hidden",
      "text-overflow": "ellipsis"
    },
    "attributes": {
      "class": "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-CoralFont"
    },
    "txtContent": "[$Time_x0020_IN.displayValue]"
  }

1 Answer 1

0

Because Date and Time are stored together, you have to get a little creative with what you compare. Here's a way to do it by looking at the locale time string. This assumes the locale time string ends up being something like 9:00 PM or 3:07 AM. This format also only covers times AFTER 9:30 am:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "box-sizing": "border-box",
    "padding": "0 2px",
    "overflow": "hidden",
    "text-overflow": "ellipsis"
  },
  "attributes": {
    "class": "=if(indexOf(toLocaleTimeString(@currentField),'P') >= 0 || (Number(substring(toLocaleTimeString(@currentField),0,indexOf(toLocaleTimeString(@currentField),':'))) > 9 && Number(substring(toLocaleTimeString(@currentField),indexOf(toLocaleTimeString(@currentField),':')+1,indexOf(toLocaleTimeString(@currentField),':')+3))>30),'sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-CoralFont sp-field-borderAllRegular sp-field-borderAllSolid','')"
  },
  "txtContent": "=@currentField.displayValue"
}
1
  • thank you for your help, does this work with 24h format? And from the preview it only affects the first entry in the list rather than the whole column, tried doing some modification to the code but to no avail, I'm thinking maybe I have to use a loop, or am I doing something wrong?
    – BobbyE
    Jun 20 at 10:26

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.