+ Reply to Thread
Results 1 to 20 of 20

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

  1. #1
    Registered User
    Join Date
    03-11-2022
    Location
    London, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    15

    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/
    Attached Files Attached Files
    Last edited by AliGW; 03-15-2022 at 06:22 AM. Reason: Irrelevant section of title removed - this is a help forum!!!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    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
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-11-2022
    Location
    London, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    15

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    Thank you for your reply..

    I have added some more information to my post, hopefully it explains the issue a bit clearer.

    Thanks again

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    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.

  5. #5
    Registered User
    Join Date
    03-11-2022
    Location
    London, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    15

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    Thank you for your help..

    I'm sorry it is rather confusing I know.. I didn't personally design the original format but am trying to alter/fix it so it actually works correctly. Any simplification you can advise would be ideal.

    In the attached example (Staff Sheet 1 - Correct) I have added some staff with start/finish times in the boxes we want to be included in the row 6 and 9 count that work correctly. These times give the desired outcome in rows 6 and 9...

    In the second example (Staff Sheet 1 - Error) if I add a staff member (G14) with a start time of 1900 (I14) and a finish time of 0500 (J14) the count in row 9 reads correctly with 1 staff member between the hours of 1900-0500. If I add another member in G15 with a start time of 1900 and a finish time of 0700 you will see the count in row 6 goes to -1 and row 9 goes to +1 which is incorrect. The count should show 2 staff members from B9 to M9 and then drop to 1 through to O9. Row 6 should stay at 0 as there is no staff during those times.

    I hope that makes sense.. If you can offer a simple way of calculating these desired results it would be very much appreciated.

    Thank you again for your help.
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    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
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-11-2022
    Location
    London, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    15

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    Thank you so much for all of that!!

    I can see what you mean and will go back to start and collate a more simplified data set and then work from there for the final analysis. Thank you for your example.. It is very helpful and makes my starting point a lot clearer.

    For rows 6 and 9, I want the total number of staff per hour to be displayed so we can clearly see areas of poor staffing etc. Which, as you said should be easier to calculate if I have the data contained in a simpler data set.

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    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

  9. #9
    Registered User
    Join Date
    03-11-2022
    Location
    London, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    15

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    Hi.. Hopefully there is an easy solution to this but I can't seem to figure it out..

    I have dramatically simplified the calculations with your recommendations.

    On the 'Data' worksheet I have a simple table where I input the staff members name, start and finish times down columns C, D and E respectively.

    Then on 'Sheet 1' I have used the below COUNTIFS code to calculate the total staff on shift per hour:

    =COUNTIFS(Data!$D$5:$D$26,"<="&B6,Data!$E$5:$E$26,">="&B6+1/24)

    This works perfectly fine for any shift that does not cross or involve midnight (00:00). Any shift that involves or crosses midnight is not counted, for example we have staff that do night shifts, such as 19:00-07:00, which is not recognised by this code.

    Do you have any ideas as to how I can count shifts that cross midnight in the hourly staff count?

    For example:

    If I enter 07:00 in D5 and 19:00 in E5 on the 'Data' worksheet I correctly get a count of 1 in cells C13-C24 on 'Sheet 1'. However,
    If I enter 19:00 in D5 and 07:00 in E5 on the 'Data' worksheet I incorrectly get no count recognised in 'Sheet 1'. I require 'Sheet 1' to update cells C25-C29 and C6-C12 to have a count of 1.

    Thank you again for all your help with this, it is very much appreciated
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,562

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    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.)
    Glenn



  11. #11
    Registered User
    Join Date
    03-11-2022
    Location
    London, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    15

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    Sorry Glenn,

    I didn't realise posting on two forums was advised against.. Was just trying to reach as many people as possible to get a fast solution, but I do apologise. I will definitely ensure I do not do this again.

    Thanks for taking the time to let me know and for posting the cross-post URL..

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,562

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

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

  13. #13
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    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
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    03-11-2022
    Location
    London, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    15

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    Thank you so much for your time assisting with this...

    The above fix works fine for shifts that include or go over midnight but now it causes an error with day shifts..

    For example a shift from 07:00 - 19:00 now adds a count to every hour on the count in the results sheet, column C..

    I am sorry this is so complicated.. I really do appreciate all your assistance

  15. #15
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    OK, let me have another look...

  16. #16
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    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.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    03-11-2022
    Location
    London, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    15

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    Hi..

    Thank you so much for this, it looks amazing!

    I have noticed a small problem, which hopefully will be easy to fix for you but with my limited Excel knowledge I am struggling to find the cause..

    The count is out by 1 hour either at the start or finish of the shift time depending on the given time

    For example, if I enter a 07:00-19:00 shift the count on the results worksheet begins at 06:00 and if I enter a 19:00-07:00 shift the count ends at 07:00, instead of 06:00.

    Do you have any idea how I can resolve this small issue?

    Thanks again

  18. #18
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    Hi,

    Try the following in C6 copied down

    Formula: copy to clipboard
    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

  19. #19
    Registered User
    Join Date
    03-11-2022
    Location
    London, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    15

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

    Thank you so much.. It works perfectly

    Honestly, Thank you so much for all your time and effort helping to sort this out.. It is very much appreciated!

  20. #20
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Please help.. trouble counting total number of staff per hour over a 24hr period

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Counting within a 24 hour period
    By kija in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-21-2020, 08:17 PM
  2. Replies: 1
    Last Post: 05-18-2015, 07:20 AM
  3. Counting Staff by Hour
    By jpickral in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-29-2014, 03:45 PM
  4. Replies: 3
    Last Post: 09-25-2013, 02:26 PM
  5. Replies: 3
    Last Post: 06-21-2013, 10:17 PM
  6. Trouble charting total staff assigned per hour in a 24hr weekly layout
    By VFsBawlS in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-07-2013, 09:18 PM
  7. Counting the number of calls within a half-hour period
    By JonathanMoore in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-06-2012, 06:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1