+ Reply to Thread
Results 1 to 8 of 8

Headcount Within Month

  1. #1
    Registered User
    Join Date
    05-11-2008
    MS-Off Ver
    2010
    Posts
    91

    Headcount Within Month

    I'm trying to figure out a function to where Excel will calculate headcount. For example if a person worked any day in that month, I need Excel to calculate that as 1 headcount for that month. Also if that person is leaving within the coming month's I need to forecast that person leaving also. I've tried the following function:

    Please Login or Register  to view this content.
    A1=Start Date
    C1=End Date
    B1=First Day of the Month in Question

    This IF statement works fine, but it does not capture headcount for people who only worked for a few days or so in the month. For example if A1=8/5/09, C1=8/10/09, and B1=8/1/09, then someone under this circumstance would fall through the cracks so to speak. Can someone help me with this? I'm so close, but yet so far. TIA...

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Headcount Within Month

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Which version of XL are you using ?

    In essence:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-11-2008
    MS-Off Ver
    2010
    Posts
    91

    Re: Headcount Within Month

    Thanks for your quick reply. I'm using Excel 2003. I've attached a sample file. The way it is set up now works with the exception of the items highlighted in yellow on rows 24-26. I hope this helps clarify my problem.
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Headcount Within Month

    Adapting the earlier solution

    Please Login or Register  to view this content.
    If you activate the Analysis ToolPak you can look at using EOMONTH / EDATE etc... and if you decide you want to apportion headcount you can look at NETWORKDAYS etc...

  5. #5
    Registered User
    Join Date
    05-11-2008
    MS-Off Ver
    2010
    Posts
    91

    Re: Headcount Within Month

    DonkeyOte,

    Thank you very much for your help. This will really help with my compensation forecast at work. Good stuff...

  6. #6
    Registered User
    Join Date
    05-11-2008
    MS-Off Ver
    2010
    Posts
    91

    Re: Headcount Within Month

    And btw, what does the "--" do in front of your formula. I notice that when I remove it the value becomes "FALSE." I've just never used that before and it seems like a nice little trick.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Headcount Within Month

    It (--) is double unary and is one method by which we can coerce a Boolean/Logical output (True/False) to it's integer equivalent... in native XL True equates to 1 and False to 0.

    Coercion can be achieved any number of ways, eg:

    =1*TRUE

    =0+TRUE

    =TRUE/1

    the double unary is regarded as ever so slightly quicker than all of the above though unlikely to be noticeable for the most part (use whichever approach makes sense (this includes using an IF)).

  8. #8
    Registered User
    Join Date
    05-11-2008
    MS-Off Ver
    2010
    Posts
    91

    Re: Headcount Within Month

    I see. Wow and I thought my Excel skills were pretty good. I guess I've got a lot to learn Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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