+ Reply to Thread
Results 1 to 4 of 4

Calculate headcount based on hourly

  1. 10-09-2022, 06:08 AM #1
    rickytripleh
    rickytripleh is offline
    Registered User
    Join Date
    04-29-2020
    Location
    Kuala Lumpur
    MS-Off Ver
    365
    Posts
    13

    Calculate headcount based on hourly

    Hi Experts,

    I'm doing some headcount and wish to have some figure in hourly.

    Trying to figure any formula can be used to auto calculate based on different working shift types :

    Ex of working shift types :
    08:00 - 17:00 : i have 11 headcount
    09:00 - 18:00 : i have 9 headcount
    10:00 - 19:00 : i have 6 headcount
    12:00 - 21:00 : i have 0 headcount
    13:00 - 22:00 : i have 17 headcount

    Solution looking for :

    08:00 = ?
    09:00 = ?
    10:00 = and so on
    11:00
    12:00
    13:00
    14:00
    15:00
    16:00
    17:00
    18:00
    19:00
    20:00
    21:00

    Attached is my sample of workbook, hope for a great solution.

    Thank you in advance
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-09-2022, 06:17 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,800

    Re: Calculate headcount based on hourly

    Try this in C10 copied across and down:

    =SUMPRODUCT((--LEFT($B$3:$B$7,2)/24<=$B10)*(--MID($B$3:$B$7,9,2)/24>=$B10+(1/24))*C$3:C$7)
    Last edited by AliGW; 10-09-2022 at 06:20 AM.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 10-09-2022, 09:16 AM #3
    rickytripleh
    rickytripleh is offline
    Registered User
    Join Date
    04-29-2020
    Location
    Kuala Lumpur
    MS-Off Ver
    365
    Posts
    13

    Re: Calculate headcount based on hourly

    Hi AliGW,

    Million Thanks~

    Much Appreciated
    Reply With QuoteRegister To Reply
  4. 10-09-2022, 09:38 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,800

    Re: Calculate headcount based on hourly

    Glad to have helped.

    If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculate Headcount by department, based on Hire date and Termination date
    By Cornelia in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-11-2020, 07:42 AM
  2. Trying to calculate shift time (headcount) between two times
    By natrajc in forum Excel General
    Replies: 3
    Last Post: 04-04-2019, 10:50 PM
  3. How to add new Headcount based on criteria
    By Deenah in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2018, 05:10 AM
  4. [SOLVED] Calculate Hourly Labor cost
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-28-2015, 01:23 AM
  5. [SOLVED] Calculate hourly average data
    By isidorian in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-20-2014, 06:34 AM
  6. Help !!! Need to breakup Shiftwise Headcount count into interval level Headcount
    By cdvipul in forum Excel General
    Replies: 1
    Last Post: 07-17-2013, 11:34 AM
  7. calculate hourly data
    By mantiso in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-20-2008, 11:49 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1