+ Reply to Thread
Results 1 to 8 of 8

Calculate Number of Employees Who Were At Work Each Hour?

  1. #1
    Registered User
    Join Date
    08-30-2015
    Location
    Impala
    MS-Off Ver
    10
    Posts
    15

    Calculate Number of Employees Who Were At Work Each Hour?

    I have been all over trying to figure this out. I attached a link at the bottom of this post to one discussion that was helpful but didn't solve my problem.

    I need to know how many employees I have working each hour of the day based on a 24/7 business with swing shifts and graveyard shifts.

    Below is the layout of the sheet going from left to right, A to I and top to bottom, from 1 to 12.

    One of the problems I may be having is dealing with military time and the shifts that go past midnight (working from 8pm to 10am). I hard entered the numbers just to see if something would work. Any ideas on how to better format the numbers would be helpful.

    This is the formula that I have been working with. It's located in cell D3:

    =IF((AND($B3>=D$1, $C3<=D$2)),0,1)

    0 100 200 300 400 500
    Start Time End Time 100 200 300 400 500 600
    emp1 830 430 1 1 1 1 0 0
    emp2 1730 530 1 1 1 1 1 0
    emp3 1330 130 1 0 0 0 0 0
    emp4 1400 230 1 1 0 0 0 0
    emp5 1530 330 1 1 1 0 0 0
    emp6 1200 2400 1 1 1 1 1 1
    emp7 2401 1200 1 1 1 1 1 1
    emp8 900 1900 1 1 1 1 1 1
    emp9 2000 1000 1 1 1 1 1 1
    emp10 1730 500 1 1 1 1 0 0



    http://www.excelforum.com/excel-form...each-hour.html

  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: Calculate Number of Employees Who Were At Work Each Hour?

    Hi,

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    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
    08-30-2015
    Location
    Impala
    MS-Off Ver
    10
    Posts
    15

    Re: Calculate Number of Employees Who Were At Work Each Hour?

    Here is the file. The number 1's should only be in the cells where the employee is working. When an employee starts at 1700 hours (or 5pm) and works until 0700 hours (or 7am) there should be a number 1 in each hourly box the employee was working.

    I hope this helps a bit.

    https://www.excelforum.com/attachmen...1&d=1513118277
    Attached Files Attached Files

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculate Number of Employees Who Were At Work Each Hour?

    I'm not sure if you want a 1 when there's overlap with part of the hour or all of the hour?

    Your times in rows 1 and 2 and also in columns C and D also contain dates which will throw off the calculations, so I removed those dates (leaving just the times) and then used this formula in E3 copied across and down

    =IF(($C3<=E$1)+($D3>=E$2)+($D3<$C3)=2,1,0)

    Does that do what you want?
    Attached Files Attached Files
    Audere est facere

  5. #5
    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: Calculate Number of Employees Who Were At Work Each Hour?

    Does the attached help

    I've changed the hourly time slots on rows 1&2 and columns C&D so that they just show the time , not a date/Time

    I've also catered for times that don't straddle midnight.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-30-2015
    Location
    Impala
    MS-Off Ver
    10
    Posts
    15

    Re: Calculate Number of Employees Who Were At Work Each Hour?

    Quote Originally Posted by daddylonglegs View Post
    =IF(($C3<=E$1)+($D3>=E$2)+($D3<$C3)=2,1,0)
    Does that do what you want?
    It does, thanks.

    Quote Originally Posted by daddylonglegs View Post
    Your times in rows 1 and 2 and also in columns C and D also contain dates which will throw off the calculations, so I removed those dates
    I was sure the date and time thing was messing with the formula. How did you remove the date part? I need a quick solution as I have hundreds of such entries in column C and D and need a mass solution rather than changing each one.

    Quote Originally Posted by daddylonglegs View Post
    I'm not sure if you want a 1 when there's overlap with part of the hour or all of the hour?
    Is there a solution to including the overlap? At least for one end of the shift?

  7. #7
    Registered User
    Join Date
    08-30-2015
    Location
    Impala
    MS-Off Ver
    10
    Posts
    15

    Re: Calculate Number of Employees Who Were At Work Each Hour?

    Quote Originally Posted by Richard Buttrey View Post
    I've also catered for times that don't straddle midnight.
    Explain how you accomplished this, please.

    Otherwise, you boys from the UK are crushing it! Thanks!

  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: Calculate Number of Employees Who Were At Work Each Hour?

    Quote Originally Posted by cr2crf View Post
    Explain how you accomplished this, please.

    Otherwise, you boys from the UK are crushing it! Thanks!
    Notice how the formula I gave you (I don't think I'd copied it from the new row 7 I added to the rest of the table) has an IF condition.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It first tests if the end time D3, is less than the start time C3 - whihc implies the times straddle midnight. If that's the case then the first part of the IF condition is used, i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If however D3>C3, i.e. both times are on the same day then the second element of the IF formula is used. i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. Replies: 5
    Last Post: 04-17-2017, 09:36 PM
  2. Number of employees on schedule per hour
    By bensorensen1019 in forum Excel General
    Replies: 1
    Last Post: 04-12-2017, 01:15 PM
  3. Track number of employees scheduled per hour
    By nestocima in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-18-2016, 07:17 PM
  4. Calculate employees working from hour to hour
    By otter in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2014, 08:57 AM
  5. Calculate # of employees each hour; sumif formulas
    By sprdsheet in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-05-2014, 12:29 PM
  6. Average number of employees per hour
    By phiphika1453 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 04-22-2013, 03:25 AM
  7. calculate time for employees in 24 hour operation
    By HoweRich in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-17-2006, 03:33 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