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.