This site uses cookies to deliver our services and to show you relevant ads and job listings.
By using our site, you acknowledge that you have read and understand our Cookie Policy, Privacy Policy, and our Terms of Service.
Your use of Stack Overflow’s Products and Services, including the Stack Overflow Network, is subject to these policies and terms.

Join us in building a kind, collaborative learning community via our updated
Code of Conduct.

Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

your cells contains text, only cells with numbers can be formatted as date, time etc. Try to use formulas VALUE(LEFT(...)), and formatting will work. Also applying conditional formatting will be easier. If you still can't solve it, then please post what exactly you've tried and what was the output.
– Máté JuhászJun 27 '16 at 9:06

1 Answer
1

If you want to use conditional formatting, comparing to a time value, you need time values in your columns. At the moment, there is a blank just in front of every time string (" 08:45") which prevents this string from being recognized as a time value. Remove the blank with the TRIM() function after extracting the part, like in =TRIM(LEFT(D2,1,6)).

Next, in the conditional format, compare each time value with a time:

=(A2 > TIME(8,45,0))

and

=(B2 < TIME(17,0,0))

This way, you can easily enter and check the beginning and ending time.

The question states that the "time values" are extracted as the left and right 5 characters of another cell, which would include the colon, so what you're seeing as the need for TRIM is really just bad formatting in the question. Also, even if TRIM would help, what's left is still a text string, not something Excel can compare to a time value.
– fixer1234Jun 27 '16 at 17:01