Calculating Employee Headcount by month.

Hi,

I need calculate monthly total employee headcount for the past few years using employee transaction data. I have 2 columns of start dates (Temp and Full-Time), Status column(Active/Inactive), and Termination Dates. I am finding that there are 2 ways i can approach this problem. The first is to take a running count of employees based solely on their start and end dates. The second is to begin with the current months total number of employees and work my way backwards by adding the terminations for the ith month and subtracting the new hires from the ith month. I am having difficulty translating either approach into an excel formula. I have attached a sample of my employee data.

Thank you in advance.

It would help to see some expected results with your sample.

How far back do you want to go?
It looks like the first real date (not including 1/0/1900 or 6/9/1910) is 1/27/1975.
Showing everything would be (2018-1975)*12 = 516 rows of monthly calculations.

There's also a 5/7/2019 and a 11/9/2119 in there.

How are you arriving at those values? Which dates (columns) are you using?

Maybe you can adapt this...
=COUNTIFS(A:A,"active",D:D,">"&EOMONTH(I3,-1),D:D,"<="&EOMONTH(I3,0))

OK did you try to adapt my suggestion?

I did but I dont understand how to implement that without explanation on what goes where. When I try to compare your formula to the sample submission, the latter 2 criteria ranges are for the FT-Date, which are the start dates for direct hires. I'm not understanding how this will get me to my monthly headcount. Would you mind to provide a more detailed explanation?

It would be better to submit a small sample with actual results so that we can test formulas/code against those results.
That said I believe that the following will work.
1) Add a column to the table that gives the earliest hire date whether it be as a temp or full time employee. The column may be populated using: =IF(OR(C2<>"",D2<>""),MIN(C2,D2),"")
2) The formula for head count (modified from Ford's) may be written:

Formula:

Please Login or Register  to view this content.

The results are untested. I would suggest testing with a small sample for which expected results can be easily produced, then compare to the values yielded by the formula.
Let us know if you have any questions.