Make a cell Different colors for Days of the week based on schedule

[SiC MaGGoT]

Please treat me like I am a complete noob as I am not very experienced with excel. I am trying to make a schedule sheet and would like the names that are listed on the sheet to light up green on days they work or red on days they are off. Assuming they work the same days every week. So for example say employee1 works Sunday through Thursday and are off Friday and Saturday. Employee2 works only Mondays and Thursdays and so on. Is this possible?

Thanks in advance.

 

[Fluff]

Hi & welcome to MrExcel.
As long as each person always works the same days it's possible, but you would have to create a conditional formatting rule for each person.

 

[SiC MaGGoT]

Thanks Fluff. How would I go about doing this?

 

[Fluff]

How many people is it for?

 

[SiC MaGGoT]

Currently 27, but that number is going to grow soon for the upcoming holidays. I wanted to try and do this so I can quickly see who was working for the day as I am managing them remotely. I don't currently have any of their schedules as I am in training and will be starting to manage them in a couple weeks. I was hoping if I learned how to setup one box I could figure out the rest. Thanks again

 

[Fluff]

Ok, how about like

+Fluff 1.xlsm
	A	B
1
2	Name 1	Sun-Thu
3	Name 2	Mon,Thu
4	Name 3	Sun-Thu
5	Name 4	Tue, Fri
6	Name 5	Wed-Sun
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6	Expression	=TODAY()=WORKDAY.INTL(TODAY()-1,1,"1100000")	text	NO
A5	Expression	=TODAY()=WORKDAY.INTL(TODAY()-1,1,"1011011")	text	NO
A4	Expression	=TODAY()=WORKDAY.INTL(TODAY()-1,1,"1001000")	text	NO
A3	Expression	=TODAY()=WORKDAY.INTL(TODAY()-1,1,"0110111")	text	NO
A2	Expression	=TODAY()=WORKDAY.INTL(TODAY()-1,1,"0000110")	text	NO

col B shows the days they work and the text strings like "1011011" in the formula specify weekends, where 1 is true (it's a weekend) & 0 is false. The string runs Mon-Sun

 

[SiC MaGGoT]

I really appreciate it, but I have no clue what I am looking at lol

 

[Fluff]

Select all the names & set the fill colour to red, then.
Select A2, then click on conditional formatting on the home tab, new rule, use a formula & enter the formula, then select the format you want & click on Ok.
You will need to do that for each name with their own formula.

 

[SiC MaGGoT]

So I click on a Cell containing a name, click conditional formatting, select "use a formula to determine which cells to format", use this formula "=TODAY()=WORKDAY.INTL(TODAY()-1,1,"1100000", and select format and select the color green?

How do I determine the 0s and 1s for days they work? Is it just Mon,tues,wed,thurs,fri,sat,sun 0 means off and 1 means on?

 

[Fluff]

Is it just Mon,tues,wed,thurs,fri,sat,sun

That's right, but the 1 means off (ie a weekend) & 0 means it's a workday.