SOLVED

New Contributor

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

5 Replies
Best Response confirmed by ashlee963 (New Contributor)
Solution

# Re: Using MS Excel to Calculate Rolling Headcount and Turnover

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.

# Re: Using MS Excel to Calculate Rolling Headcount and Turnover

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.

# Re: Using MS Excel to Calculate Rolling Headcount and Turnover

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

# Re: Using MS Excel to Calculate Rolling Headcount and Turnover

Thank you so much, I really appreciate your help!

# Re: Using MS Excel to Calculate Rolling Headcount and Turnover

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.