Forum Discussion
Using MS Excel to Calculate Rolling Headcount and Turnover
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!
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.
16 Replies
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.
Depends on goals. If for predefined date - yes. If slice and dice with periods, departments, etc - data model is more preferable.
SergeiBaklan 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.
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.
- 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!
I'd create a pivot table based on the raw data, and filter that the way you want.
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.
- Thank you so much, I really appreciate your help!
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.
- Wow, thank you so much! This is a huge help!
