Daily Rota Formula

DatsonUK

New Member
Joined
Oct 10, 2017
Messages
2
Hi all,<o:p></o:p>
<o:p> </o:p>
I am a novice at excel and not sure if it’s possible formy idea to work.<o:p></o:p>
<o:p> </o:p>
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:p></o:p>
When I put what time each staff member is working, I simplywant to see the cell row turn a solid colour.<o:p></o:p>
<o:p> </o:p>
The idea is to see how many staff members are in duringthe day.<o:p></o:p>
<o:p> </o:p>
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]



 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
ABCDEFGHIJ
1StartEnd07:0007:3008:0008:3009:0009:3010:00
207:0009:30
308:0010:00
409:0010:00
Rota
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D21. / Formula is =ROUND(MEDIAN($A2,$B2,D$1),10)=ROUND(D$1,10)Abc
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top