Calculate headcount at the end of each month in 2019 based on hire and termination date

M

maxim642

Board Regular
Joined
Feb 4, 2021
Messages
91
Office Version
  1. 365
Platform
  1. MacOS
I want to work out the headcount at the end of each month in 2019.

I have provided a dummy dataset below. Headcount calculation is based on hire and termination date. If the termination date is blank, that means the person is still with the business and should be included in the headcount.

If the termination date falls on the last day of the month, they should still be counted within the headcount of that month.

For example, if Joe Bloggs was hired in February 2018 but has a termination date of December 2018, they should not be captured in the headcount for 2019.

I tried to share the data with XL2BB but I kept getting the spinning beach ball thing (I'm on M1 Pro Macbook Pro 16 inch).

I'd like to see the headcount splits beneath each month in columns K:V.

K would just show what the headcount was up to 31st Jan 2019, L would show up to 28th of February, and so on.

How would I apply an additional filter to this, so that I could see what the headcount was for a specific department, like Customer Care? Ideally, I'd like to be able to switch between showing the headcount for the whole company, or if I want to see it by the department I can do that also.

Thanks.


A. B. C D E F G H I J K L M N O P Q R. S T U V
Employee IDDepartmentLevelCountryHire DateTermination DateGenderEthnicityDate of BirthTenureJan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19
512485Grocery2United Kingdom31/07/201731/07/2020MaleWhite - British (United Kingdom)04/12/19961-5 years
512485Customer Care1United Arab Emirates14/06/2020MaleWhite - British (United Kingdom)04/12/19961-5 years
516478GroceryEUnited Kingdom27/09/201904/06/2021MaleNot Disclosed05/07/19891-5 years
516478Customer Care1United Arab Emirates10/05/202113/12/2021MaleNot Disclosed05/07/19896-12 months
518888Delivery OperationsESpain16/02/201821/02/2020Not DisclosedNot Disclosed13/11/19961-5 years
518888EditionsEUnited Kingdom09/08/2021Not DisclosedNot Disclosed13/11/19966-12 months
526588Restaurants4United Arab Emirates30/09/201831/03/2020MaleAsian (Not Hispanic or Latino) (United Arab Emirates)12/06/19881-5 years
526588People2Australia22/07/201914/02/2020MaleAsian (Not Hispanic or Latino) (United Arab Emirates)12/06/19886-12 months
528478Customer Care3France26/04/2021MaleWhite (Italy)09/01/19866-12 months
528478EUnited Kingdom22/11/2021MaleWhite (Italy)09/01/19860-6 months
532879Editions3Hong Kong01/09/2016FemaleMixed - Other (United Kingdom)13/09/19935 years+
532879Technology6United Kingdom27/09/2021FemaleMixed - Other (United Kingdom)13/09/19930-6 months
533215Finance6United Kingdom02/08/2021MaleAsian (Not Hispanic or Latino) (United Arab Emirates)15/08/19856-12 months
5332157United Kingdom15/11/2021MaleAsian (Not Hispanic or Latino) (United Arab Emirates)15/08/19850-6 months
544329Restaurants1United Kingdom17/09/201823/05/2019MaleNot Disclosed24/07/19806-12 months
544329Technology7United Kingdom24/05/2021MaleNot Disclosed24/07/19806-12 months
555344Finance8United Kingdom11/01/2021MaleNot Disclosed22/08/19941-5 years
555344People2Hong Kong05/05/2021MaleNot Disclosed22/08/19946-12 months
556204Restaurants5Australia21/10/2019MaleAsian - Indian (United Kingdom)19/08/19841-5 years
556204Restaurants1Spain24/02/202031/08/2020MaleAsian - Indian (United Kingdom)19/08/19846-12 months
584172Finance7United Kingdom05/06/201709/08/2019FemaleWhite - British (United Kingdom)16/08/19861-5 years
584172Consumer5United Kingdom26/04/2021FemaleWhite - British (United Kingdom)16/08/19866-12 months
585669Grocery1Netherlands06/01/202030/06/2020FemaleWhite (Australia)23/08/19900-6 months
585669Restaurants1Netherlands05/11/201831/01/2021FemaleWhite (Australia)23/08/19901-5 years
589056Restaurants6France21/09/201517/04/2020MaleWhite - British (United Kingdom)18/12/19901-5 years
589056Restaurants4Kuwait25/11/201831/12/2020MaleWhite - British (United Kingdom)18/12/19901-5 years
594321Consumer3France18/02/201930/08/2019FemaleNot Disclosed11/06/19956-12 months
594321Delivery Operations2United Kingdom05/04/202121/08/2021FemaleNot Disclosed11/06/19950-6 months
596821Consumer2Taiwan01/09/201924/04/2020FemaleWhite - British (United Kingdom)25/01/19916-12 months
596821RestaurantsESpain02/08/202127/08/2021FemaleWhite - British (United Kingdom)25/01/19910-6 months
597502Finance4United Kingdom29/05/201820/12/2019FemaleWhite - British (United Kingdom)17/02/19851-5 years
597502Customer Care1United Arab Emirates21/10/2018FemaleWhite - British (United Kingdom)17/02/19851-5 years
626673Technology4United Kingdom30/09/201921/06/2020MaleNot Disclosed27/05/19736-12 months
626673Editions1United Arab Emirates25/10/2020MaleNot Disclosed27/05/19731-5 years
627772Delivery OperationsESpain19/09/201821/07/2019MaleNot Disclosed17/05/19956-12 months
627772Technology5United Kingdom18/10/2021MaleNot Disclosed17/05/19950-6 months
629532Delivery OperationsEHong Kong03/06/201930/09/2020MaleNot Disclosed18/07/19941-5 years
629532Technology3United Kingdom09/12/201931/07/2020MaleNot Disclosed18/07/19946-12 months
631816Delivery Operations1Hong Kong02/07/201831/12/2020MaleChinese (Hong Kong)04/12/19911-5 years
631816Editions4United Arab Emirates08/08/2021MaleChinese (Hong Kong)04/12/19916-12 months
634248Restaurants1United Kingdom10/10/201820/04/2020MaleChinese (Hong Kong)21/03/19971-5 years
634248Restaurants2Taiwan01/04/201910/04/2020MaleChinese (Hong Kong)21/03/19971-5 years
634691Consumer5United Kingdom06/08/201804/10/2019FemaleAsian - Chinese (United Kingdom)24/10/19831-5 years
634691EditionsEUnited Kingdom08/10/202109/11/2021FemaleAsian - Chinese (United Kingdom)24/10/19830-6 months
635516Consumer9United Kingdom01/07/201931/07/2020MaleAsian - Bangladeshi (United Kingdom)27/09/19901-5 years
635516Restaurants3Singapore19/07/2021MaleAsian - Bangladeshi (United Kingdom)27/09/19906-12 months
637592Restaurants2Belgium03/04/201826/07/2019FemaleChinese (Hong Kong)28/11/19941-5 years
637592Technology5United Kingdom16/09/2019FemaleChinese (Hong Kong)28/11/19941-5 years
637831Restaurants3United Kingdom11/06/201820/07/2020MaleNot Disclosed26/06/19991-5 years
637831Restaurants1United Kingdom19/10/202031/05/2021MaleNot Disclosed26/06/19996-12 months
640811Technology4United Kingdom05/08/201919/06/2020MaleBlack - British (United Kingdom)07/03/19816-12 months
640811Editions3France23/08/2021MaleBlack - British (United Kingdom)07/03/19816-12 months
649136Delivery OperationsESpain05/09/201815/07/2019MaleChinese (Singapore)13/01/19886-12 months
649136Delivery Operations4Australia25/10/201620/03/2020MaleChinese (Singapore)13/01/19881-5 years
656647People3United Kingdom01/02/2021FemaleWhite - British (United Kingdom)25/11/19931-5 years
656647RestaurantsEUnited Kingdom19/07/2021FemaleWhite - British (United Kingdom)25/11/19936-12 months
659298Grocery4United Kingdom22/08/201609/07/2021MaleBlack - African (United Kingdom)28/10/19871-5 years
659298Delivery OperationsEUnited Kingdom25/06/201831/01/2020MaleBlack - African (United Kingdom)28/10/19871-5 years
664470Special ProjectsEUnited Kingdom05/11/201814/07/2019MaleWhite - Other European (United Kingdom)16/04/19896-12 months
664470GroceryEUnited Kingdom31/01/202023/04/2020MaleWhite - Other European (United Kingdom)16/04/19890-6 months
674137EditionsEUnited Kingdom13/11/2020FemaleNot Disclosed04/03/19851-5 years
674137Legal8United Kingdom12/07/2021FemaleNot Disclosed04/03/19856-12 months
683068People6United Kingdom02/05/201715/11/2019FemaleNot Disclosed17/11/19941-5 years
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Does this do what you want?

22 02 26.xlsm
KLMNOPQRSTUV
1Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19
2232424252425232226262525
Head Count
Cell Formulas
RangeFormula
K2:V2K2=LET(eom,EOMONTH(K1,0),COUNT(FILTER($I2:$I64,($E2:$E64<=eom)*(($F2:$F64>=eom)+($F2:$F64="")),"")))
 
Upvote 0
Solution
Peter_SSs said:
Does this do what you want?

22 02 26.xlsm
KLMNOPQRSTUV
1Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19
2232424252425232226262525
Head Count
Cell Formulas
RangeFormula
K2:V2K2=LET(eom,EOMONTH(K1,0),COUNT(FILTER($I2:$I64,($E2:$E64<=eom)*(($F2:$F64>=eom)+($F2:$F64="")),"")))
Click to expand...
I think this is working. I'm just double-checking some periods.

If it's not too much trouble, would you mind explaining what the formula is actually doing, in layman's terms? I've never used the LET function before. Why is this preferred over SUMPRODUCT or COUNTIFS? And where you have embedded a FILTER, why is it looking at column I (Date of Birth)? What is that filter actually doing?

Thanks for your help. Greatly appreciated.
 
Upvote 0
The main function in my formula is the FILTER function, not the LET function, so it is not a matter of choosing LET over SUMPRODUCT but choosing a combination of FILTER & COUNT over SUMPRODUCT.
SUMPRODUCT should be fine for this task but it can be a bit sluggish if the ranges are large so I was just choosing the more modern & generally more efficient functions.

LET enables the use of variables in a formula. This is an efficiency since a value only needs to be calculated once. In this case I wanted to use the 'end of month date' twice in the formula. Using LET it only needs to be calculated once and then the result is inserted in both places where I have subsequently used eom in my formula.


FILTER($I2:$I64,($E2:$E64<=eom)*(($F2:$F64>=eom)+($F2:$F64="")),"")

This is taking the DoB column and only returning (ie filtering) those DoB's where
Hire Date is on or before the end of month date
* = AND
Termination Date is on or after the end of month date OR (+) Termination Date is blank

It is most likely irrelevant for your circumstance as I assume you would never have a head count of zero, but I used the DoB column since those values are numerical so the COUNT function gives a good way to get the final number even if it was zero.

If interested, the equivalent SUMPRODUCT formula would be
Excel Formula: 
=SUMPRODUCT(--($E2:$E64<=EOMONTH(K1,0)),($F2:$F64>=EOMONTH(K1,0))+($F2:$F64=""))
 
Upvote 0
Peter_SSs said:
The main function in my formula is the FILTER function, not the LET function, so it is not a matter of choosing LET over SUMPRODUCT but choosing a combination of FILTER & COUNT over SUMPRODUCT.
SUMPRODUCT should be fine for this task but it can be a bit sluggish if the ranges are large so I was just choosing the more modern & generally more efficient functions.

LET enables the use of variables in a formula. This is an efficiency since a value only needs to be calculated once. In this case I wanted to use the 'end of month date' twice in the formula. Using LET it only needs to be calculated once and then the result is inserted in both places where I have subsequently used eom in my formula.


FILTER($I2:$I64,($E2:$E64<=eom)*(($F2:$F64>=eom)+($F2:$F64="")),"")

This is taking the DoB column and only returning (ie filtering) those DoB's where
Hire Date is on or before the end of month date
* = AND
Termination Date is on or after the end of month date OR (+) Termination Date is blank

It is most likely irrelevant for your circumstance as I assume you would never have a head count of zero, but I used the DoB column since those values are numerical so the COUNT function gives a good way to get the final number even if it was zero.

If interested, the equivalent SUMPRODUCT formula would be
Excel Formula: 
=SUMPRODUCT(--($E2:$E64<=EOMONTH(K1,0)),($F2:$F64>=EOMONTH(K1,0))+($F2:$F64=""))
Click to expand...
Thank you very much for taking the time to explain the formula. I've never used the LET function before, though it sounds pretty nifty.

I tried the SUMPRODUCT formula above and can confirm that works too.

If I wanted to add an additional parameter to either of the above formulas, which one would be easier to do that it? At the moment, the formula is counting the number of employees in a given period. What if I wanted to count just the number of females?

For reference, in column G I have a gender category. Is it possible to embed an additional filter that will count just "females"?

Thanks again for your help.
 
Upvote 0
maxim642 said:
If I wanted to add an additional parameter to either of the above formulas, which one would be easier to do that it?
Click to expand...
They both use pretty similar syntax for adding categories so really not one easier than the other.
For column K it would be

=LET(eom,EOMONTH(K1,0),COUNT(FILTER($I2:$I64,($E2:$E64<=eom)*(($F2:$F64>=eom)+($F2:$F64=""))*($G2:$G64="Female"),"")))

=SUMPRODUCT(--($E2:$E64<=EOMONTH(K1,0)),($F2:$F64>=EOMONTH(K1,0))+($F2:$F64=""),--($G2:$G64="Female"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

nburaq
  • Solved
Calculate interval between dates based on input at another columns
Replies
2
Views
127
awoohaw
awoohaw
A
  • Question
Lookup+sum+max
Replies
2
Views
387
aarky
A
U
  • Question
year by year headcount snapshot with only start and end date
2
Replies
11
Views
659
awoohaw
awoohaw
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
359
kevin9999
K
M
  • Question
Best solution to consolidate values that occur on multiple dates/lines
Replies
5
Views
263
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top