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
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 ID | Department | Level | Country | Hire Date | Termination Date | Gender | Ethnicity | Date of Birth | Tenure | Jan-19 | Feb-19 | Mar-19 | Apr-19 | May-19 | Jun-19 | Jul-19 | Aug-19 | Sep-19 | Oct-19 | Nov-19 | Dec-19 | |
512485 | Grocery | 2 | United Kingdom | 31/07/2017 | 31/07/2020 | Male | White - British (United Kingdom) | 04/12/1996 | 1-5 years | |||||||||||||
512485 | Customer Care | 1 | United Arab Emirates | 14/06/2020 | Male | White - British (United Kingdom) | 04/12/1996 | 1-5 years | ||||||||||||||
516478 | Grocery | E | United Kingdom | 27/09/2019 | 04/06/2021 | Male | Not Disclosed | 05/07/1989 | 1-5 years | |||||||||||||
516478 | Customer Care | 1 | United Arab Emirates | 10/05/2021 | 13/12/2021 | Male | Not Disclosed | 05/07/1989 | 6-12 months | |||||||||||||
518888 | Delivery Operations | E | Spain | 16/02/2018 | 21/02/2020 | Not Disclosed | Not Disclosed | 13/11/1996 | 1-5 years | |||||||||||||
518888 | Editions | E | United Kingdom | 09/08/2021 | Not Disclosed | Not Disclosed | 13/11/1996 | 6-12 months | ||||||||||||||
526588 | Restaurants | 4 | United Arab Emirates | 30/09/2018 | 31/03/2020 | Male | Asian (Not Hispanic or Latino) (United Arab Emirates) | 12/06/1988 | 1-5 years | |||||||||||||
526588 | People | 2 | Australia | 22/07/2019 | 14/02/2020 | Male | Asian (Not Hispanic or Latino) (United Arab Emirates) | 12/06/1988 | 6-12 months | |||||||||||||
528478 | Customer Care | 3 | France | 26/04/2021 | Male | White (Italy) | 09/01/1986 | 6-12 months | ||||||||||||||
528478 | E | United Kingdom | 22/11/2021 | Male | White (Italy) | 09/01/1986 | 0-6 months | |||||||||||||||
532879 | Editions | 3 | Hong Kong | 01/09/2016 | Female | Mixed - Other (United Kingdom) | 13/09/1993 | 5 years+ | ||||||||||||||
532879 | Technology | 6 | United Kingdom | 27/09/2021 | Female | Mixed - Other (United Kingdom) | 13/09/1993 | 0-6 months | ||||||||||||||
533215 | Finance | 6 | United Kingdom | 02/08/2021 | Male | Asian (Not Hispanic or Latino) (United Arab Emirates) | 15/08/1985 | 6-12 months | ||||||||||||||
533215 | 7 | United Kingdom | 15/11/2021 | Male | Asian (Not Hispanic or Latino) (United Arab Emirates) | 15/08/1985 | 0-6 months | |||||||||||||||
544329 | Restaurants | 1 | United Kingdom | 17/09/2018 | 23/05/2019 | Male | Not Disclosed | 24/07/1980 | 6-12 months | |||||||||||||
544329 | Technology | 7 | United Kingdom | 24/05/2021 | Male | Not Disclosed | 24/07/1980 | 6-12 months | ||||||||||||||
555344 | Finance | 8 | United Kingdom | 11/01/2021 | Male | Not Disclosed | 22/08/1994 | 1-5 years | ||||||||||||||
555344 | People | 2 | Hong Kong | 05/05/2021 | Male | Not Disclosed | 22/08/1994 | 6-12 months | ||||||||||||||
556204 | Restaurants | 5 | Australia | 21/10/2019 | Male | Asian - Indian (United Kingdom) | 19/08/1984 | 1-5 years | ||||||||||||||
556204 | Restaurants | 1 | Spain | 24/02/2020 | 31/08/2020 | Male | Asian - Indian (United Kingdom) | 19/08/1984 | 6-12 months | |||||||||||||
584172 | Finance | 7 | United Kingdom | 05/06/2017 | 09/08/2019 | Female | White - British (United Kingdom) | 16/08/1986 | 1-5 years | |||||||||||||
584172 | Consumer | 5 | United Kingdom | 26/04/2021 | Female | White - British (United Kingdom) | 16/08/1986 | 6-12 months | ||||||||||||||
585669 | Grocery | 1 | Netherlands | 06/01/2020 | 30/06/2020 | Female | White (Australia) | 23/08/1990 | 0-6 months | |||||||||||||
585669 | Restaurants | 1 | Netherlands | 05/11/2018 | 31/01/2021 | Female | White (Australia) | 23/08/1990 | 1-5 years | |||||||||||||
589056 | Restaurants | 6 | France | 21/09/2015 | 17/04/2020 | Male | White - British (United Kingdom) | 18/12/1990 | 1-5 years | |||||||||||||
589056 | Restaurants | 4 | Kuwait | 25/11/2018 | 31/12/2020 | Male | White - British (United Kingdom) | 18/12/1990 | 1-5 years | |||||||||||||
594321 | Consumer | 3 | France | 18/02/2019 | 30/08/2019 | Female | Not Disclosed | 11/06/1995 | 6-12 months | |||||||||||||
594321 | Delivery Operations | 2 | United Kingdom | 05/04/2021 | 21/08/2021 | Female | Not Disclosed | 11/06/1995 | 0-6 months | |||||||||||||
596821 | Consumer | 2 | Taiwan | 01/09/2019 | 24/04/2020 | Female | White - British (United Kingdom) | 25/01/1991 | 6-12 months | |||||||||||||
596821 | Restaurants | E | Spain | 02/08/2021 | 27/08/2021 | Female | White - British (United Kingdom) | 25/01/1991 | 0-6 months | |||||||||||||
597502 | Finance | 4 | United Kingdom | 29/05/2018 | 20/12/2019 | Female | White - British (United Kingdom) | 17/02/1985 | 1-5 years | |||||||||||||
597502 | Customer Care | 1 | United Arab Emirates | 21/10/2018 | Female | White - British (United Kingdom) | 17/02/1985 | 1-5 years | ||||||||||||||
626673 | Technology | 4 | United Kingdom | 30/09/2019 | 21/06/2020 | Male | Not Disclosed | 27/05/1973 | 6-12 months | |||||||||||||
626673 | Editions | 1 | United Arab Emirates | 25/10/2020 | Male | Not Disclosed | 27/05/1973 | 1-5 years | ||||||||||||||
627772 | Delivery Operations | E | Spain | 19/09/2018 | 21/07/2019 | Male | Not Disclosed | 17/05/1995 | 6-12 months | |||||||||||||
627772 | Technology | 5 | United Kingdom | 18/10/2021 | Male | Not Disclosed | 17/05/1995 | 0-6 months | ||||||||||||||
629532 | Delivery Operations | E | Hong Kong | 03/06/2019 | 30/09/2020 | Male | Not Disclosed | 18/07/1994 | 1-5 years | |||||||||||||
629532 | Technology | 3 | United Kingdom | 09/12/2019 | 31/07/2020 | Male | Not Disclosed | 18/07/1994 | 6-12 months | |||||||||||||
631816 | Delivery Operations | 1 | Hong Kong | 02/07/2018 | 31/12/2020 | Male | Chinese (Hong Kong) | 04/12/1991 | 1-5 years | |||||||||||||
631816 | Editions | 4 | United Arab Emirates | 08/08/2021 | Male | Chinese (Hong Kong) | 04/12/1991 | 6-12 months | ||||||||||||||
634248 | Restaurants | 1 | United Kingdom | 10/10/2018 | 20/04/2020 | Male | Chinese (Hong Kong) | 21/03/1997 | 1-5 years | |||||||||||||
634248 | Restaurants | 2 | Taiwan | 01/04/2019 | 10/04/2020 | Male | Chinese (Hong Kong) | 21/03/1997 | 1-5 years | |||||||||||||
634691 | Consumer | 5 | United Kingdom | 06/08/2018 | 04/10/2019 | Female | Asian - Chinese (United Kingdom) | 24/10/1983 | 1-5 years | |||||||||||||
634691 | Editions | E | United Kingdom | 08/10/2021 | 09/11/2021 | Female | Asian - Chinese (United Kingdom) | 24/10/1983 | 0-6 months | |||||||||||||
635516 | Consumer | 9 | United Kingdom | 01/07/2019 | 31/07/2020 | Male | Asian - Bangladeshi (United Kingdom) | 27/09/1990 | 1-5 years | |||||||||||||
635516 | Restaurants | 3 | Singapore | 19/07/2021 | Male | Asian - Bangladeshi (United Kingdom) | 27/09/1990 | 6-12 months | ||||||||||||||
637592 | Restaurants | 2 | Belgium | 03/04/2018 | 26/07/2019 | Female | Chinese (Hong Kong) | 28/11/1994 | 1-5 years | |||||||||||||
637592 | Technology | 5 | United Kingdom | 16/09/2019 | Female | Chinese (Hong Kong) | 28/11/1994 | 1-5 years | ||||||||||||||
637831 | Restaurants | 3 | United Kingdom | 11/06/2018 | 20/07/2020 | Male | Not Disclosed | 26/06/1999 | 1-5 years | |||||||||||||
637831 | Restaurants | 1 | United Kingdom | 19/10/2020 | 31/05/2021 | Male | Not Disclosed | 26/06/1999 | 6-12 months | |||||||||||||
640811 | Technology | 4 | United Kingdom | 05/08/2019 | 19/06/2020 | Male | Black - British (United Kingdom) | 07/03/1981 | 6-12 months | |||||||||||||
640811 | Editions | 3 | France | 23/08/2021 | Male | Black - British (United Kingdom) | 07/03/1981 | 6-12 months | ||||||||||||||
649136 | Delivery Operations | E | Spain | 05/09/2018 | 15/07/2019 | Male | Chinese (Singapore) | 13/01/1988 | 6-12 months | |||||||||||||
649136 | Delivery Operations | 4 | Australia | 25/10/2016 | 20/03/2020 | Male | Chinese (Singapore) | 13/01/1988 | 1-5 years | |||||||||||||
656647 | People | 3 | United Kingdom | 01/02/2021 | Female | White - British (United Kingdom) | 25/11/1993 | 1-5 years | ||||||||||||||
656647 | Restaurants | E | United Kingdom | 19/07/2021 | Female | White - British (United Kingdom) | 25/11/1993 | 6-12 months | ||||||||||||||
659298 | Grocery | 4 | United Kingdom | 22/08/2016 | 09/07/2021 | Male | Black - African (United Kingdom) | 28/10/1987 | 1-5 years | |||||||||||||
659298 | Delivery Operations | E | United Kingdom | 25/06/2018 | 31/01/2020 | Male | Black - African (United Kingdom) | 28/10/1987 | 1-5 years | |||||||||||||
664470 | Special Projects | E | United Kingdom | 05/11/2018 | 14/07/2019 | Male | White - Other European (United Kingdom) | 16/04/1989 | 6-12 months | |||||||||||||
664470 | Grocery | E | United Kingdom | 31/01/2020 | 23/04/2020 | Male | White - Other European (United Kingdom) | 16/04/1989 | 0-6 months | |||||||||||||
674137 | Editions | E | United Kingdom | 13/11/2020 | Female | Not Disclosed | 04/03/1985 | 1-5 years | ||||||||||||||
674137 | Legal | 8 | United Kingdom | 12/07/2021 | Female | Not Disclosed | 04/03/1985 | 6-12 months | ||||||||||||||
683068 | People | 6 | United Kingdom | 02/05/2017 | 15/11/2019 | Female | Not Disclosed | 17/11/1994 | 1-5 years |