• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Have Excel count in a rota to determine number of staff on duty by grade.

K

karlhr

New Member
I have to create a simple rota which requires to indicate

The number of staff on duty

The number of each grade on duty

Any short fall or over staffing e.g. have the shortfall show in RED

To make things easier to ready D = Day (10h 43m), N = Night (10h 43m)

9-5 = Regular working day (7h 30m)

Those staff doing either D or N have to work 7 shifts every 14 days

Those staff doing 9-5 have to work 10 shifts every 14 days.

The rota needs to cover 6 weeks

There is a list of names, which includes all staff available for duty

Previously I have created simple rota first column contains a list of names with the top row indicating dates and day to make things easier to read each date has 2 columns below split to Day night
 
Karlhr

What you have done previously is heading in the right direction

You may need to add a couple of columns for the Grade and other skills that each staff member has next to their names

At the bottom of each column you can then addup the number of each Grade using a Sumproduct or Sumifs.

You can use condittional formatting to highlight excess or shortages of a Particular grade.
 
Thank you for your response. I got some of the way but one thing that is bugging me. How do I get the column to add up all the D or N separately so that I can confirm how many staff are on duty. To make life easier I have two columns per day, one for days, one for nights. How do I assign the value. I suspect I need to specify this somewhere else and refer to it.
 
Karlhr

I have made a quick mockup of what I propsoed

It is at http://rapidshare.com/files/394210474/Rota.xlsx.html


The File uses Conditional Formatting to set the cell color when rostered


The file contains two summary areas based on Sumproduct and Sumifs


If you are using Excel 2003 or earlier delete the bottom area which uses Sumifs.


If you want to know more about Sumproduct, look here http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/
 
Phew . . . . many thanks for your response.


A clear example makes life much easier. I had a eureka moment in the night woke up and realised "" helped me to create a rota which summarises. e.g. =IF(A2="D",1,0) Logical :)combined with your examples, makes a simpler result. Am going to work on this over the next few days to create a complete sheet which will help my staff when they are putting the rota together. I got to delegate the task . . . but still required to prep the excel workbook.
 
Hi karlhr,

I created something a long time ago that might give you what your looking for.

A customisable sheet with room for 200 staff, 20 shift types inc hols sickleave etc, 10 depts and 10 ranks/grades. It prints a rota of the year for each person, a shift signing in sheet (if reqd) a 7 day report of who is working where.

A visual graphs of the year ahead by dept or skills mix


As you can see, I started to type a description of what it does and realised it would be easier to let you see it working but I'm new to the forum and have yet to work out how to make the document available.


If its of interest,

let me know and I'll look at how to get it to you.


Cheers
 
Tom,

I would be most interested in your Rota, can you email it to me? If so my email is my username in this forum @gmail.com.


Thanks in advance,

Jason
 
You must log in or register to reply here.
Back
Top