Forum Discussion

Copper Contributor

Dec 31, 2020

Solved

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] ...

excel

Formulas and Functions

HansVogelaar
Dec 31, 2020
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.

HansVogelaar

MVP

Dec 31, 2020

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.

sdryfuse
Copper Contributor
Sep 28, 2021
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!
- HansVogelaar
  MVP
  Sep 28, 2021
  sdryfuse
  
  I'd create a pivot table based on the raw data, and filter that the way you want.
  - sdryfuse
    Copper Contributor
    Sep 28, 2021
    HansVogelaar
    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!!