Daily Rota Formula

[DatsonUK]

Hi all,<o></o>
<o> </o>
I am a novice at excel and not sure if it’s possible formy idea to work.<o></o>
<o> </o>
I want to create a sheet which tells me the total numberof staff I have on duty during the day. With times every half hour from 0700 to2230. <o></o>
When I put what time each staff member is working, I simplywant to see the cell row turn a solid colour.<o></o>
<o> </o>
The idea is to see how many staff members are in duringthe day.<o></o>
<o> </o>
I hope this makes sense and I appreciate any help given.

i.e
0700 0930
0800 1000
0900 1000

[TABLE="width: 100, align: left"]
<tbody>[TR]
[TD]0700[/TD]
[TD]0730[/TD]
[TD]0800[/TD]
[TD]0830[/TD]
[TD]0900[/TD]
[TD]0930[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]in[/TD]
[TD]in[/TD]
[TD]in[/TD]
[TD]in[/TD]
[TD]in[/TD]
[TD]in[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]in[/TD]
[TD]in[/TD]
[TD]in[/TD]
[TD]in[/TD]
[TD]in[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]in[/TD]
[TD]in[/TD]
[TD]in[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this suit your purpose?
- Start and End times in columns A:B
- Select D2:J4
- Conditional Formatting (Home ribbon tab) -> New rule.. -> Use a formula to determine which cells to format -> enter the formula shown below my screen shot -> Format... -> On the Fill tab choose your colour -> OK -> OK

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Start	End		07:00	07:30	08:00	08:30	09:00	09:30	10:00
2	07:00	09:30
3	08:00	10:00
4	09:00	10:00
Rota

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2	1. / Formula is =ROUND(MEDIAN($A2,$B2,D$1),10)=ROUND(D$1,10)	Abc

 

[DatsonUK]

This is brilliant.

thank you for your help!

 

[Peter_SSs]

You are welcome. Glad it helped.