Excel help.. Staff rotas

Droolinggimp

Droolinggimp

Droolinggimp

Soldato
Joined
16 May 2004
Posts
7,169
Location
Derby
I am looking to find a way to make my rotas easier to do ..

I have 3 full-time staff and 1 part-time. Plus me.
What I am looking for is to get Excel to total up the daily hours used by my staff and then total them up for the full week.

For example.

staff monday
me 6 - 4
per1 7 - 1
per2 11:30 - 8:30
per3 day off
per4 day off

total hrs 23 hrs

Ok with me so far.????

It is 23 hours on monday because, Me and per2 have 1 hour dinner break each and this isnt included in hours worked. Per1 doesnt have a dinner due to working only 6 hours (has a break though).

How can I get excel to total the daily hours and still take the 2, 1 hour dinner breaks off? I also need to make sure excel doesnt take an 1 hour dinners of the staff who only work 6 hours. I need to use this system for every day of the week..

I hope you can understand what I am trying to achieve here.. any help is very very welcome.
 
droolinggimp said:
I am looking to find a way to make my rotas easier to do ..

I have 3 full-time staff and 1 part-time. Plus me.
What I am looking for is to get Excel to total up the daily hours used by my staff and then total them up for the full week.

For example.

staff monday
me 6 - 4
per1 7 - 1
per2 11:30 - 8:30
per3 day off
per4 day off

total hrs 23 hrs

Ok with me so far.????

It is 23 hours on monday because, Me and per2 have 1 hour dinner break each and this isnt included in hours worked. Per1 doesnt have a dinner due to working only 6 hours (has a break though).

How can I get excel to total the daily hours and still take the 2, 1 hour dinner breaks off? I also need to make sure excel doesnt take an 1 hour dinners of the staff who only work 6 hours. I need to use this system for every day of the week..

I hope you can understand what I am trying to achieve here.. any help is very very welcome.
Click to expand...

Just add another column with lunch hours in it. Then do b2-c2 i.e.


staff monday start end lunch totals
you...... 6...............4..........1 =(b2-c2)-d2
per1..... 7..............1...........0 e.t.c.
per2..... 11.30........ 8.30.......1 e.t.c
................................................................sum


or you could do a simple formula.
Does that help or have I misunderstood ?

I'll do you a sheet if you give me your mail address. Take about 10 mins to knock up and send.
 
Last edited:
Yeah it sort of helps .. but im really after it minusing the hour dinners automaticaly with out the use of a new colum if you see what I mean..

I will upload a picture of the blank rota (paper one that we have to use at work)to my NLT space and you can get an idea of what i am trying to achieve.


A formula is the best bet according to my missus..

pics here

email here
 
Last edited:
=IF(HOUR(C2)-HOUR(B2)>6,HOUR(C2)-HOUR(B2)-1,HOUR(B2)-HOUR(C2))

or IF(C2-B2>6,C2-B2-1,B2-C2)

probably a simpler way of doing it.
 
You could be onto a winner there.. I will try that out once I have cleaned the house..

Would that formula put the total number of hours in a cell lower down the page like on my paper rota I have uploaded or would I have to add to it to make it total it up on the lower cell. Hope thats understandable to you.


If you could the whole rota with formulas etc like it nees to be done at work, I might even send somthing nice your way..

If you need more info let me know

Back on in 1 hour.. Cleaning to do:(
 
You will be best entering the break/lunch times just incase they do have a break or you don't. You might also want to use this column for times when your staff may need to nip out for say 10-15 mins.

TrUz
 
The breaks are paid for so they are not taken away from the total hours of the working day.. Only dinners as they are not paid. The staff have to clock out and back in 1 hour later.
 
You must log in or register to reply here.
Back
Top Bottom