I'm maintaining an attendance table in the following format.

01 Wed IN | 01 Wed OUT
08:27     | 17:16
08:36     | 14:31
08:38     | 17:07
08:32     | 17:05
08:47     | 17:05
08:31     | 17:04
08:16     | 17:13
08:31     | 17:13
08:29     | 17:05
08:40     | 17:06
08:32     | 17:02
08:52     | 17:05
08:36     | 17:08
08:30     | 18:37

I want to apply conditional formatting for "IN" column and "OUT" column. If IN time > 8:45, the font in that cell should be RED. If OUT time < 17:00, the font in that cell should be RED.

The data in IN and OUT columns is a result of LEFT(cell, 5) and RIGHT(cell,5) from another column that records every IN and OUT entry of the staff.

I have tried conditional formatting options but I'm obviously doing something wrong. Tried formatting the cells as Time, Custom, etc.

  • 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ász Jun 27 '16 at 9:06

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+0 > TIME(8,45,0))


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

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

edit (thanks to the commenters):
Of course, LEFT() still returns a string which, if compared to a time (a number), yields wrong results. Either add a 0 in the conditional format formula, or in the cell itself (=LEFT(cell, 5) + 0. This would be preferable as you can now format or calculate with these times.

  • 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. – fixer1234 Jun 27 '16 at 17:01
  • would =(A2 + 0 > TIME(8,45,0)) be better? , should convert text to number – PeterH Nov 27 at 11:04

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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