# Headcount Within Month

1. ## 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. ## 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. ## 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.

4. ## 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. ## 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. ## 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. ## 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. ## 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.

##### Users Browsing this Thread

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

#### 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