Conditional Formatting if Time is later than 5:00PM

JSpears

I am using the TIME function in my Planned Start Time and Planned End Time. Planned End Time is calculated using the Minutes Duration column (=TIME([Planned Start Time]@row) + [Minutes Duration]@row).

I want to set up conditional formatting to highlight times that are outside of business hours. I tried to set conditional formatting using greater than but that did not work. Any ideas?

I have tried to add a checkmark column that will check if the Planned Start or Planned is greater than 5:00 PM but I'm not able to get it to work:

Thank you in advance!

jmyzk_cloudsmart_jp

Hi @JSpears

Your "=TIME([Planned Start Time]@row) + [Minutes Duration]@row" is an excellent way to use the TIME function.

Unfortunately, the Time function's value is a text, not a number. So, we can not use operators like ">" , ">=", and "<=".😅

I would convert the text time values to numbers by converting them to 24-hour format and then using the text functions to convert them to numbers.

I used the Sheet Summary fields in my demo sheet to set the End Business and Start Business. (I use the Start Business value to cope with a situation where a task starts before the End Business but ends or stops before the Start Business, very early morning, for example. If you do not have to cope with such a situation, you can remove the third OR condition in the example formula below.)

Planned Start Time Number Format

=VALUE(LEFT([Planned Start Time 24 Hour Fomat]@row, 2)) + VALUE(RIGHT([Planned Start Time 24 Hour Fomat]@row, 2)) / 60

Start or Stop Outside Business

=IF(OR([Planned Start Time Number Format]@row > [End Business]#, [Planned End Time Number Format]@row > [End Business]#, [Planned End Time Number Format]@row < [Start Business]#), 1, 0)

https://app.smartsheet.com/b/publish?EQBCT=d8f6943f7d36422d85a6b3b596cbfc48