Conditional formatting with JSON with time in a date colunm

Question

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]"
  }

Answer 1

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": "[email protected]"
}