SOLVED

Using MS Excel to Calculate Rolling Headcount and Turnover

%3CLINGO-SUB%20id%3D%22lingo-sub-2024505%22%20slang%3D%22en-US%22%3EUsing%20MS%20Excel%20to%20Calculate%20Rolling%20Headcount%20and%20Turnover%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2024505%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20Excel%20spreadsheet%20with%20columns%20including%3A%3C%2FP%3E%3CUL%3E%3CLI%3Epersonnel%20names%3C%2FLI%3E%3CLI%3Edate%26nbsp%3Bstarted%26nbsp%3B%3C%2FLI%3E%3CLI%3Edate%20left%20(blank%20cell%20if%20still%20current)%3C%2FLI%3E%3CLI%3Eduration%20calculation%20(%3DIF(%20%5BDate%20Left%5D%20%26gt%3B0%2C%26nbsp%3B%5BDate%20Left%5D%26nbsp%3B-%20%5BDate%20Started%5D%20%2C%20IF(%26nbsp%3B%5BDate%20Started%5D%26nbsp%3B%26gt%3B0%2C%20TODAY()%20-%26nbsp%3B%5BDate%20Started%5D%26nbsp%3B%2C%22%22))%2F365)%3C%2FLI%3E%3C%2FUL%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22start%20end%20dates.JPG%22%20style%3D%22width%3A%20308px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243568i99C0DE2DE6E11EB6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22start%20end%20dates.JPG%22%20alt%3D%22start%20end%20dates.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20use%20this%20information%20to%20calculate%20the%20number%20of%20personnel%20on%20any%20given%20day%3F%20i.e.%20to%20create%20something%20similar%20to%20this%3F%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rolling%20headcount.JPG%22%20style%3D%22width%3A%20378px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243569i2242497E118C130A%2Fimage-dimensions%2F378x225%3Fv%3D1.0%22%20width%3D%22378%22%20height%3D%22225%22%20role%3D%22button%22%20title%3D%22rolling%20headcount.JPG%22%20alt%3D%22rolling%20headcount.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20also%20interested%20in%20calculating%20the%20monthly%20average%20number%20of%20personnel%2C%20as%20well%20as%20the%20number%20of%20new%20starts%20and%20the%20number%20that%20left%26nbsp%3Bin%20a%20given%20month%2C%20to%20identify%20trends.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22fluctuations.JPG%22%20style%3D%22width%3A%20470px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243570i500D777124C9E0C5%2Fimage-dimensions%2F470x227%3Fv%3D1.0%22%20width%3D%22470%22%20height%3D%22227%22%20role%3D%22button%22%20title%3D%22fluctuations.JPG%22%20alt%3D%22fluctuations.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%26nbsp%3Bfor%20your%20help!%20I%20enjoy%20trying%20to%20figure%20this%20stuff%20out%2C%20but%20I'm%20a%20bit%20stuck%20with%20this%20one%20and%20I'm%20excited%20for%20your%20ideas!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2024505%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2024680%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20MS%20Excel%20to%20Calculate%20Rolling%20Headcount%20and%20Turnover%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2024680%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F915025%22%20target%3D%22_blank%22%3E%40ashlee963%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20In%20H2%2C%20enter%20the%20following%20array%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((%24B%242%3A%24B%2411%26lt%3B%3DG2)*(IF(%24C%242%3A%24C%2411%3D%22%22%2CG2%2C%24C%242%3A%24C%2411)%26gt%3B%3DG2))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2024715%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20MS%20Excel%20to%20Calculate%20Rolling%20Headcount%20and%20Turnover%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2024715%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F915025%22%20target%3D%22_blank%22%3E%40ashlee963%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20O2%3A%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS(%24B%242%3A%24B%2411%2C%22%26gt%3B%3D%22%26amp%3BM2%2C%24B%242%3A%24B%2411%2C%22%26lt%3B%22%26amp%3BEDATE(M2%2C1))%3C%2FP%3E%0A%3CP%3EIn%20P2%3A%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS(%24C%242%3A%24C%2411%2C%22%26gt%3B%3D%22%26amp%3BM2%2C%24C%242%3A%24C%2411%2C%22%26lt%3B%22%26amp%3BEDATE(M2%2C1))%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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)
 

start end dates.JPG

 

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

 

rolling headcount.JPG

 

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. 

 

fluctuations.JPG

 

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

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

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

Wow, thank you so much! This is a huge help!
Thank you so much, I really appreciate your help!

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