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

0700	0730	0800	0830	0900	0930	1000
in	in	in	in	in	in
		in	in	in	in	in
				in	in	in

<tbody>
</tbody>

 

[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.