Excel – Microsoft Excel TIME related Conditional Formatting

conditional formattingmicrosoft excel

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.

Best Answer

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))

and 

=(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.

Related Question