+ Reply to Thread
Results 1 to 6 of 6

Count the number of staff working between times based on staff position

  1. #1
    Registered User
    Join Date
    03-29-2014
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2013
    Posts
    6

    Count the number of staff working between times based on staff position

    Hi,

    I am needing a formula to solve this problem..

    I would like to count the number of staff that I have working for each hour of the day based on the individual staff position.

    In my current spreadsheet I have a roster of staff that are split into different job types in column E and their start time in column K and finish time in column L.

    Also the times of the day start at 7am and finish the following morning at 8am (yes this is 25 hours!!)

    I have managed to do a total count using the following formula
    =SUMPRODUCT(--(($K$7:$K$49<(BB17+BC17)/2)+($L$7:$L$49>(BB17+BC17)/2)+($K$7:$K$49>$L$7:$L$49)=2))
    the cells BB17 and BC17 are the defining times of the day i.e. 07:00 for BB17 and 07:59 for BC17
    and this works very well.

    The staff positions are listed as numbers from 1-6

    Any help would be much appreciated.

  2. #2
    Registered User
    Join Date
    04-07-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    34

    Re: Count the number of staff working between times based on staff position

    Could you please attach a Sample Spreadsheet so that your expectation would be much more clearer

  3. #3
    Registered User
    Join Date
    03-29-2014
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Count the number of staff working between times based on staff position

    Here is the file that I am working on, the formula is needed on the SHIFT SHEET tab.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Count the number of staff working between times based on staff position

    This solution puts helper rows in $X$52:$AV$54 of the Shift Sheet. The first helper row counts Telephonists with the function:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dispatchers with the function:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Team Leaders/Shift Leaders* with the function:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then the range BI17:BK41 with the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    *There is no distinction made between TL/SL in rows 8 through 12 so I only filled the SL column.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    03-29-2014
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Count the number of staff working between times based on staff position

    Thanks JeteMc,

    I was trying to do this without 'Helper' cells, but this solution does work so I think i will go with this.

    Once again many thanks.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Count the number of staff working between times based on staff position

    You're Welcome and thank you for the feedback. If you haven't already, please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope you have a good day.

+ 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. Count Over-time of Staff and Count the no. of staff in given range of time
    By pahackerz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-04-2015, 12:37 PM
  2. Count number of staff per month, with joiners and leavers
    By Ricardo Mass in forum Excel General
    Replies: 8
    Last Post: 11-25-2014, 12:58 PM
  3. [SOLVED] Macro to update Score Card Summary when the new staff add-in or existing staff deleted
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-12-2014, 02:43 PM
  4. Providing how many times staff members have been late by staff ID
    By SG56001235 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2013, 10:47 PM
  5. Staff Planner - How To Set Up UserForm to Generate Data into Staff & Date Spreadsheet
    By Marie Snell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2013, 11:04 PM
  6. Replies: 3
    Last Post: 08-07-2011, 02:30 AM
  7. Replies: 0
    Last Post: 11-18-2007, 07:24 PM

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