Microsoft Excel TIME related Conditional Formatting
up vote
0
down vote
favorite
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.
microsoft-excel conditional-formatting
edited Jun 28 '16 at 4:51
fixer1234
17.4k144281
asked Jun 27 '16 at 9:02
Sunny
111
add a comment |
up vote
0
down vote
favorite
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.
microsoft-excel conditional-formatting
edited Jun 28 '16 at 4:51
fixer1234
17.4k144281
asked Jun 27 '16 at 9:02
Sunny
111
your cells contains text, only cells with numbers can be formatted as date, time etc. Try to use formulasVALUE(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
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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.
microsoft-excel conditional-formatting
edited Jun 28 '16 at 4:51
fixer1234
17.4k144281
asked Jun 27 '16 at 9:02
Sunny
111
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.
microsoft-excel conditional-formatting
microsoft-excel conditional-formatting
edited Jun 28 '16 at 4:51
fixer1234
17.4k144281
asked Jun 27 '16 at 9:02
Sunny
111
edited Jun 28 '16 at 4:51
fixer1234
17.4k144281
asked Jun 27 '16 at 9:02
Sunny
111
edited Jun 28 '16 at 4:51
fixer1234
17.4k144281
edited Jun 28 '16 at 4:51
fixer1234
17.4k144281
edited Jun 28 '16 at 4:51
fixer1234
17.4k144281
17.4k144281
asked Jun 27 '16 at 9:02
Sunny
111
asked Jun 27 '16 at 9:02
Sunny
111
asked Jun 27 '16 at 9:02
Sunny
111
111
your cells contains text, only cells with numbers can be formatted as date, time etc. Try to use formulasVALUE(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
add a comment |
your cells contains text, only cells with numbers can be formatted as date, time etc. Try to use formulasVALUE(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
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
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
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
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.
answered Jun 27 '16 at 9:17
user1016274
1,206713
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
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
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.
answered Jun 27 '16 at 9:17
user1016274
1,206713
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
add a comment |
up vote
0
down vote
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.
answered Jun 27 '16 at 9:17
user1016274
1,206713
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
add a comment |
up vote
0
down vote
up vote
0
down vote
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.
answered Jun 27 '16 at 9:17
user1016274
1,206713
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.
answered Jun 27 '16 at 9:17
user1016274
1,206713
edited Nov 27 at 11:24
answered Jun 27 '16 at 9:17
user1016274
1,206713
answered Jun 27 '16 at 9:17
user1016274
1,206713
answered Jun 27 '16 at 9:17
user1016274
1,206713
1,206713
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
add a comment |
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
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
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
would
=(A2 + 0 > TIME(8,45,0)) be better? , should convert text to number– PeterH
Nov 27 at 11:04
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1093912%2fmicrosoft-excel-time-related-conditional-formatting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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