Using MS Excel to Calculate Rolling Headcount and Turnover

[ashlee963]

I have an Excel spreadsheet with columns including:

• personnel names
• date started 
• date left (blank cell if still current)
• duration calculation (=IF( [Date Left] >0, [Date Left] - [Date Started] , IF( [Date Started] >0, TODAY() - [Date Started] ,""))/365)

How can I use this information to calculate the number of personnel on any given day? i.e. to create something similar to this?

I'm also interested in calculating the monthly average number of personnel, as well as the number of new starts and the number that left in a given month, to identify trends. 

Thank you for your help! I enjoy trying to figure this stuff out, but I'm a bit stuck with this one and I'm excited for your ideas! 

[#2 · Accepted solution]

@ashlee963 

1) In H2, enter the following array formula confirmed with Ctrl+Shift+Enter:

=SUMPRODUCT(($B$2:$B$11<=G2)*(IF($C$2:$C$11="",G2,$C$2:$C$11)>=G2))

Fill down.

[#3]

@ashlee963 

In O2:

=COUNTIFS($B$2:$B$11,">="&M2,$B$2:$B$11,"<"&EDATE(M2,1))

In P2:

=COUNTIFS($C$2:$C$11,">="&M2,$C$2:$C$11,"<"&EDATE(M2,1))

Fill down.

[#4]

Wow, thank you so much! This is a huge help!

[#5]

Thank you so much, I really appreciate your help!

[#6]

@ashlee963 

3) In N2 as an array formula confirmed with Ctrl+Shift+Enter:

=SUM(IF(IF(IF($C$2:$C$11="",EDATE(M2,1),$C$2:$C$11)<EDATE(M2,1),IF($C$2:$C$11="",EDATE(M2,1),$C$2:$C$11),EDATE(M2,1))>IF($B$2:$B$11>M2,$B$2:$B$11,M2),IF(IF($C$2:$C$11="",EDATE(M2,1),$C$2:$C$11)<EDATE(M2,1),IF($C$2:$C$11="",EDATE(M2,1),$C$2:$C$11),EDATE(M2,1))-IF($B$2:$B$11>M2,$B$2:$B$11,M2),0))/(EDATE(M2,1)-M2)

Fill down.

[#7]

Hello. Thank you for this. How could I use this to create a pivot table and slice this information by department, age group, gender, enthicity, etc…. Thanks!

[#8]

@sdryfuse 

I'd create a pivot table based on the raw data, and filter that the way you want.

[#9]

@Hans Vogelaar 

Thanks!  I have my two tables...the first table is all the info related to the employee, start date, department, etc...  The second table is where I get my calculations.  The date I'm pointing at and the equations for hires, terms, and headcount.  I tried to create a relationship between the two tables, but I am not having any luck.  Let me know if you need more detail.  I can send you the two tables I'm using.  Thanks for your help!!

[#10]

@sdryfuse 

Perhaps you can use PowerQuery and PowerPivot for this, but that's not my forte - I hope that someone else can help. If you don't get new replies here, I'd start a new discussion. That will attract more attention.

[#11]

@sdryfuse 

In general yes, that could be done with data model. It will be great to know on which version of Excel you are and on which platform (Windows, Mac, etc). Plus desirably to have sample file to play with it - that's not 5-minutes job to generate solution.

[#12]

@ashlee963 

Am I missing the point?  Surely a headcount simply requires COUNTIFS to quantify the total number of hires up to and including the census date minus the total number of departures (adjusted by the headcount at the dawn of time if non-zero).  Turnover is simply the change between to census dates, though the differences between two COUNTIFS can be combined into one by adding a 'started after' criterion.

These days, I might dress the solution up a bit and introduce a Lambda function 'HEADCOUNTλ'

= LAMBDA(Date,
      COUNTIFS(Table1[Date Started],"<="&Date)
      -  COUNTIFS(Table1[Date Left],"<="&Date) )

so that the worksheet formula reads

= HEADCOUNTλ(CensusDate)

I am so glad to see the back of CSE though.  I had schooled myself to use absolute references only and commit all formulae with CSE but it is such a relief to get rid of the practice.

[#13]

@Peter Bartholomew 

Depends on goals. If for predefined date - yes. If slice and dice with periods, departments, etc - data model is more preferable.

[#14]

@Sergei Baklan 

You are right of course.  The 'slicing and dicing' of large data sets is what BI was built for but, for smaller datasets that do not need to be loaded from external sources, dynamic array solutions can give database methods a run for their money.  Maybe by defining a function,

= TURNOVERλ(StartDate, Period, "ProductEngineering")

If you want to give the end user the option of selecting data through the use of slicers, then the data model is the natural solution.

[#15]

@Peter Bartholomew 

Yes, often the question we asked is the small part of entire picture and we may only guess what is behind. Moreover, not always OP knows what exactly (s)he'd like to have.

By the way, that's why I'm not fun of "best response" logic here. Best answer for one is not best for another one. "My computer doesn't work, what shall I do?" has million of answers, who knows what's the best in concrete case?

[#16]

@Sergei Baklan I appreciate everyone's help!  I'm trying to bring two tables together to allow me to slice by department, age, gender, ethnicity, etc... for head count, hires, separations, and turnover.  I'm using a month end date as the the predefined date to go off of when creating the pivot table.  I attached the example workbook that has both tables involved.  I have the formula setup in data2 table to do this, but I would like it to be more flexible.  I hope this helps.  Thank you again.

[#17]

@Sergei Baklan I’ve decided to work on this some more.  I have a date table, my employee table (department, gender, age, ethnicity) and the table I use to figure monthly head counts, hires, terms and turnover percentage based on month end dates.   I have all three tables in data model of my pivot table/chart but I can’t bring it all together to allow me to slice these stats down by the categories I want to show.  Thanks for any help you can give me!