Trouble counting total number of staff per hour over a 24hr period

Hello,

I am attempting to automatically count the number of staff we have per hour over a 24 hour period. We have staff starting and finishing at different times throughout the day/night and currently count it manually. I have put together the attached worksheet which seems to work for majority of shifts but comes in to trouble at certain times... I believe this is because we can't continuously loop the times.

We enter the staff members name, start and finish times in one of the designated boxes in the 'Daily staffing' worksheet and then want the staff per hour count to automatically update in rows 6 and 8 in the same worksheet - these cells B6-O6 and B9-O9 update correctly for majority of shifts entered but fails if you enter certain times.. for example a 1900-0700 shift in one of the start/finish columns on the worksheet displays incorrect data in the cells of row 6 and 8.

For example if we enter a staff members name in G14 with a start time of 0700 in I14 and a finish time of 1500 in J14 row 6 updates correctly and shows a total of 1 across B6-J6 correlating with the shift time 0700-1500.

BUT if we enter a shift time of 1900-0700 in cells I14 and J14 respectively the count in row 6 and 8 does not correlate correctly.

I can see issues in the code but I just can't find a solution if anyone has any ideas please?

Thank you

Secretly posted here, too: https://www.mrexcel.com/board/thread...eriod.1198964/

Hello and welcome to the forum.

You need to clearly tell us which data you are trying to analyse (which sheet, which cells) and manually create some expected results clearly explaining which the result cells are located. It's certainly not clear at the moment

I don't fully understand the requirement and would like to see a worked example with a few names and the manually calculated results you expect.

That said it seems an unusual way to conduct arithmetic with times. You're entering strings of text when it's normal to use actual time values like say 07:00 for 7 am. i.e the underlying number value is 0.2916667 = 7/24 of a day.

Neither is it clear why you actually need all then helper columns from U onwards. I would have thought that any calculation you wnat to perform to get a result in row 6 could be done by simply applying your rules to the proper time data values in columns like A&J

As I say, tell us what the results should be when you've entered say four or five names with varying start and end times and maybe we can come up with a much simplified model.

Hi,

This could be simplified considerably if the data was recorded in a normalised 2 dimensional database.
The current layout is mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

It also uses merged cells. Most of us avoid them like the plague (or covid) since they just cause too many problems for reason that needn't detain us just at the moment

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality. The order of the columns is not particularly important but would

So before you get too far with this I'd create a single sheet database that contains the following columns

Date
Cost Centre - Adastra, Cat 2, HCP...etc
Name
Start Time - entered as a proper time number, e.g. 07:00, 16:30
Finish Time - ditto

I've added the start of a data layout in the attached. I've used I've started a reporting row on the same sheet but in practice this would be contained on its own sheet.

I've used the same times that you currently have but don't really understand what your rules are counting. Is it staff working within a particular hour or some sort of cumulative count? You also seem to distinguish between two types of cost centre with your current rows 8:33

However if you can explain your rule in words for how you count the results in your current rows 6 & 9 no doubt we can incorporate those rules in the new layout

The other thing to bear in mind with the reporting aspect, is who is the report designed for?
If it's for a main board report where presentation, colours, different fonts etc. as well as a non 'rectangular' layout is concerned then a purpose built layout with functions that feed off the database will be needed.

If however the report is to enable you to manage and understand the data and any relationships then it's often better to create a Pivot Table based on the data.

When you've got a larger data set with real variances between hours and cost centres and if you're still struggling then let me know. Any statistc you need to know can almost certainly be obtained from the normalised database without need for helper columns and rows

Welcome (back) to the forum.

Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

1) if you have cross-posted, and

2) the URL of the cross posts.

That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

(Note: since you are new here, on THIS occasion I have done it for you.)

As I said.... feel free to x-post... just don't keep it secret!

See the attached

I've added two helper columns to the data. Note also that on the Results sheet the formula changes after noon, i,e, from C18 to C19

I suspect there may be a more elegant and slightly simpler way to do this bit for the moment hopefully this will help

OK, let me have another look...

Please test this.

I've simplified it a bit with just one helper column in the data to identify a shift that straddles midnight.

On the Results sheet you can remove the working columns E:G. I just left them in order to build up the compound formula in column C.

I can't help thinking this could be simplified further.

The other approach of course would be to create a macro which looped down all the data rows and built up a count for each of the 24 hourly buckets as it processed each record. Anyway, let me know.

Hi,

Try the following in C6 copied down

Formula:

Please Login or Register  to view this content.

It just needed an adjustment to a couple of the B6+1/24 elements. Adding in one case and removing in another.

The 1/24 of a day, i.e. 1 hour and advances the B6 decimal time time by 1 / 24 i.e. the decimal 0.0416667

My pleasure. If I think of anything more elegant I'll let you know.