Conditional Formatting if Time is later than 5:00PM

Options
JSpears
JSpears ✭✭✭✭✭
edited 11/08/23 in Formulas and Functions

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!

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    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)



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!