staff per hour

I have been using excel to create work roster for many years now.

As my staffing pool has grown its gotten too time consuming to count each hour group manually.

reading an older post somebody used countif function, but i have not been able to duplicate it.

Thanks so much for your help.

I articulated myself poorly for my main intention.

What i was hoping to calculate is how many staff are rostered on per hourly interval.

The columns on the far right AA>AE (TIME) are how many staff are rostered per hour, 5am (3 people on shift), 6am (5 people on shift), 7am (5 people on shift), etc. (currently counted manually)

The formulae provide is very useful and i will be using it, always counted those hours manually as well.

Some staff are permanent night shift, others rotate day and night,

Hi,

First off, thank you all starting with spacecadet for something that hopefully will be useful to me in the future.

Now, I don't have any solutions or improvements, but I do have "a couple" of questions:
In relation to Shukla provided, in AC3, why 1/24? I can see how it changes if I remove it or if I use any other number instead of 24, but I just do not understand what it stands for.

My 2nd question is for spacecadet. The numbers in columns AA-AE are supposed to be the number of people who are/should be/would be actively working at the different hours stipulated?
I ask because at least the very first number, 3, is incorrect (unless they were numbers that were just thrown there for illustration purposes) making Shukla's suggestion a must.
My only addition is to suggest a select, then right-click-drag a clean column from the right and do "Shift Right and Move/Copy", instead of inserting one. You can then resize to 1 or 2, or even hide it.

A slightly separate and compound question: in the workbook provided, what is the top bar above the column letters, what is it for, how does one make that?

Thank you all, again. Cheers.

Thank your for your input.

Columns AA-AF represents the amount of staff per hour, AA column shows 3 as there's carry over from the previous roster not shown on this, they are all out of whack now due to various changes. Which is why i am trying to automate the count.

The top bar you refer too, i suspect is the grouping function which i am using to hide rows. This is done via the data tab, select a sequential row/column and they can be grouped.

hi everyone,

have applied the formulae but it is not functioning as i'd hoped once applied to the full roster.

would a change in the layout of the roster be the best option?

the formulae from what can understand is counting how many times 0500 appears, and then applies the same to the 0600 adding the value of 0500 to it?

the roster is 24 hour roster, so it needs to factor in cross over times ad well.

the code below igot from another post, but i cannot make it work as my roster is using a different layout.

=(COUNTIF($C$7:$C$12,">="&F7)-COUNTIF($B$7:$B$12,">"&F7))

Hi sc,
As per the formula you posted, I can't help you understand it because it does not correspond to either roster you provided.

You are right about the formula counting and adding the previous, but that results inaccurate when a person's shift ended at 0600, for example, and it certainly does not take into account anyone coming from a shift that started on the previous day. I think your current layout would require quite a bit of finagling to get all of it right.

As it is, your current roster contains some info that we can interpret without an conscious thought, like Shannon who starts at 1800 and ends at 0600, we know that it's 6 a.m. of the following day, but Excel can't reason that out so easily. You would need a formula with several comparisons and IF's.{shudder} As is, she starts at 1800 and finishes at 0600 of "the same day". As a matter of fact, the formula for total hours fails with Shannon and Bronwyn for that same reason; they would need two lines, 1800 to 2400 and 0000 to 0600 for each overnight, for their totals to be correct.

I don't know what kind of layout would be best, but off the top of my head I would say an expanded daily/hourly roster would suit you best.

09/21/2017 | 0000 | 0500 | 0600 | 0700 | ... | 1700 | 1800 | 1900 | 2400
Nathan
Dan
Allan
Shannon
...
Steve
Paul
Total's row with # of staff at any give hour on that date
(or you can transpose rows and columns to match your current layout... I tend to go with the shortest list going across.)
Considering your carry-over's, I would do a nine-day weekly worksheet, from, say, Sunday of the previous week to Monday of the following (on a lighter font?), just to make it easier to see the overnighters flowing in and flowing out.

At the end of each row there could be a total # of hours each staff works that day, and anyone working past midnight would appear in the following day's roster from midnight to end their shift. Also, not all shifts are 8-hour shifts and Netty's ends on the half hour...

Sorry I've made this so long, and I'm pretty sure I have said nothing you had not thought of already... As a curious person that I am, I'm interested in your final decision for the roster layout.

It seems like a growing business (congrats!) with a growing staff came to need a much larger roster and worksheet.

Cheers.