If cell, highlight

[bradg12]

Hi All,

I've created a roster in excel for my workplace.

I'm using a few drop down menus, to allow the person creating the roster to select from a list of times in the start and finish columns, along with a formula that finds the difference between the dates to show the number of hours working.

What I want to do, in addition to this, is add another page which displays the shifts in a bar graph type style. I've included a screenshot of how it would look with me physically doing it.

I'm thinking for the formula it needs to be something like;

in the 830 box, if the start time is between this time, colour the cell.

Any ideas on where to start?

Roster:

Graph I want to create based on roster start and finish times

 

[moonfish]

Considering the following layout:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Sunday	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
2	*	8:30	9:00	9:30	10:00	10:30	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00
3	person1	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
4	person2	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
5	person3	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
9	*	7/3/2011	7/4/2011	7/5/2011	7/6/2011	7/7/2011	7/8/2011	7/9/2011	*	*	*	*	*	*	*	*	*	*	*
10	*	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	*	*	*	*	*	*	*	*	*	*	*
11	person1	12:00	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
12	*	19:00	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
13	person2	12:00	12:00	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
14	*	16:00	15:00	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
15	person3	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet1

Do the following:
- Select cells B3:S5
- In the Excel menu go to Conditional Formatting > New Rule
- Choose the Rule Type: Use a formula to determine which cells to format
- Use the following format:

=AND(B$2 >= INDEX($B$11:$H$16, MATCH($A3,$A$11:$A$16,0), MATCH($A$1,$B$10:$H$10,0)), B$2 <= INDEX($B$11:$H$16, MATCH($A3,$A$11:$A$16,0)+1, MATCH($A$1,$B$10:$H$10,0)))

- Choose the format of your liking using the Format button.

 

[bradg12]

Hey Moonfish,

Thanks for taking a look at this one.

I've setup exactly what you've got here, and entered the formula into the cells you asked to do.

Nothing happened though :-s

 

[moonfish]

I'm not familiar with the layout of your version of Excel. Which version / OS are you using?

The asterisks were only to keep my table properly formatted here on the forums. A cell that contains only an asterisk in my example should be empty in Excel.


The main reason it's not working is because you didn't copy everything. The row with days is used as a means of reference.
Try entering 'Sunday' in cell B10 (without the apostrophes).

 

[bradg12]

Sorry for jumping the gun on that one!

Adding in Sunday worked like a charm, very smart.

Thanks a lot for that mate

Is the date used for anything? Do I need to keep that field in.

 

[moonfish]

Nope, I was only trying to replicate the layout from your initial screenshots.

If you change the formula around a bit you can fill this chart directly from your original Roster, without any additional required cells.

You might want to look into the way this formula works because it's currently set up to support only 3 different people. If you ever want to change this, or change your layout, you'll be better off knowing what you got

Here's my attempt at explaining the formula:

=AND(
     B$2 >= INDEX($B$11:$H$16,
          MATCH($A3,$A$11:$A$16,0),
          MATCH($A$1,$B$10:$H$10,0)
     ),
     B$2 <= INDEX($B$11:$H$16,
          MATCH($A3,$A$11:$A$16,0)+1,
          MATCH($A$1,$B$10:$H$10,0)
     )
)

• All references are made from the top left part of the area you wish to format conditionally. In your case, this is B3.
• There are two criteria for a field to be colored, they are contained in the AND() function. The first is the starting time, the second is the end time.
• The B$2 reference points to the time stamps in row 2. When considering to color C5, it will refer to C$2. The dollar sign keeps the row number from changing.
• INDEX(range,match(),match()) is used to find a value in a table, typically based on the row and column headers. In this case, the range is the roster. The first match compares the name of the person on the left of the graph with the name of the people in the roster. The second match compares the day of the graph to the day in the roster.
• The whole thing is then repeated in the second half of the AND statement, with the addition of a +1 to the first match, to find the end time.

If you want to visualize your referrences, take the formula used to format your graph and copy it to cell B3 of your spreadsheet. While your cursor is inside the B3 cell, all different cell references should be highlighted with various colors.

 

[bradg12]

Again, thanks.

I've been able to extend that to some more people other then the three in the original script.

The only problem I'm having is, if I enter a 16:00 shift, it'll start highlighting at 16:15

Anyway to make it start highlighting from 16:00.

Oddly enough, if I enter a 8:45 it'll start from 8:45.

 

[moonfish]

What's the exact formula you're using?

 

[bradg12]

=and(b$12 >= index($b$33:$h$38, match($a13,$a$33:$a$38,0), match($a$1,$b$32:$h$32,0)), b$12 <= index($b$33:$h$38, match($a13,$a$33:$a$38,0)+1, match($a$1,$b$32:$h$32,0)))

 

[bradg12]

Werid enough-

If I enter the time in, 17:45 it'll highlight the 17:45 cell.

If I gather the start time thru ='ROSTER TEMPLETE'!B8 which is the cell I select the time from a drop down menu, it'll highlight 18:00.