conditional formatting with time range

[kelvin_9]

hello all,

i'd like to use conditional formatting to identify my employee time range for easy reference
what should i do in the formula if i make a break down like, fill in yellow for who work before 11:00 and green for who work after 11:01?

NAME	SUNDAY	MONDAY
Peter	11:30-21:00 Photo	12:30-22:00 HH-Zone B
Apple	11:30-21:00 Photo	12:30-22:00 HH-Zone B
Mag	10:30-20:00 HH-Zone B	09:00-18:30 HH

on the other contrary, can i fill in the colour in column_name for my request if only one day?

NAME	SUNDAY
Peter	11:30-21:00 Photo
Apple	11:30-21:00 Photo
Mag	10:30-20:00 HH-Zone B

thanks for the guidance
kelvin

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I think that you will also need to clarify your question since we do not know exactly what your data means and at a quick look it would seem to me that the information below indicates that Mag worked both before and after.

 

[kelvin_9]

Hi Peter_SSs

apology for the confuse, hereunder i've made 2 sample for you all, the time range in the table below means the employee start time and end time
(for example, Peter on sunday, he works from 11:30 and off duty at 21:00)
sample 1, i would like to ask is it possible to set a formula in conditional formatting, fill in yellow for who work before 11:00 and green for who work after 11:01?
sample 2, if there is only one day, can i return the result on empolyee instead of the day.

sorry again for my bad language

thanks for the guidance,
kelvin
(i will make sure the update on my account details when i get back home)

 

[Peter_SSs]

i will make sure the update on my account details when i get back home

I suggest that you also investigate the following:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if this does what you want.
I am a little unsure about the changeover around 11:00 as a strat time of 11:00 or 11:01 does not fit either of these descriptions

yellow for who work before 11:00 and green for who work after 11:01

21 08 04.xlsm
	A	B	C	D
1		NAME	SUN	MON
2		a	11:30-21:00	12:30-22:00
3		b	11:30-21:00	09:30:21:00
4		c	10:30-20:00	12:30-22:00
Start Time

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B4	Expression	=AND(COUNTA($C$1:$F$1)=1,LEFT(C2,2)-11<0)	text	NO
C2:I4	Expression	=AND(COUNTA($C$1:$I$1)>1,LEFT(C2,2)-11<0)	text	NO
C2:I4	Expression	=AND(COUNTA($C$1:$I$1)>1,LEFT(C2,2)-11>=0)	text	NO
B2:B4	Expression	=AND(COUNTA($C$1:$F$1)=1,LEFT(C2,2)-11>=0)	text	NO

 

[kelvin_9]

yes, correct, thanks for the answering

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.