1

is there a way to conditional format based on the time range or time value? i have a SPO list where employees have a daily task that is due at 5pm everyday. i wanted to conditional format the column where if the time value is <=5:00pm the column will turn green and if <5:00pm will turn red.

can this be achieved in SPO? using JSON syntax or any other way?

thank you in advance!

2
  • What is the data type of your "time" column? is it date & time column or something else? Commented Aug 19, 2021 at 4:48
  • the data type i have is number but the column type is calculated value because i am extracting the time value from the modified column.
    – Abbyamante
    Commented Aug 20, 2021 at 13:43

1 Answer 1

1

This can be done with several caveats:

  • @now will return the current time (at render) in the local timezone for the user
  • toLocaleTimeString may return different formats depending on the user's browser locale
  • The expression below is assuming time in a 12 hour clock with the hour first separated with a colon from the next value. If the localetimestring is different the expression will need to be adjusted
  • If the list is open at 4:59pm it will be green and won't turn red at 5pm until the list item is either updated or the view is refreshed
  • There is no lower bounds. If the time is AM it will be green, otherwise it checks the hour is earlier than 5
  • If you want to include exactly 5:00pm in the green condition (but not 5:01pm) you'll have to extend the expression to look at the minutes as well
{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "attributes": {
    "class": "='ms-fontColor-white ' + if(indexOf(toLocaleTimeString(@now), 'AM') > -1 || Number(substring(toLocaleTimeString(@now),0,indexOf(toLocaleTimeString(@now),':'))) < 4, 'ms-bgColor-green', 'ms-bgColor-red')"
  }
}

In the above I'm using the standard Microsoft red and green classes for background color (with white text). If you'd rather apply this to background-color with a custom color value you can just take the expression starting with the if and swap out the class names with your color values.

1
  • thank you so much. i dont know how to tweak this code (i have no IT background) but this gives me a good start. i tried the exact syntax you provided, the background turned green for all the value and the time format changed to whole number.
    – Abbyamante
    Commented Aug 20, 2021 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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