I have included some sample data to use as a reference point.
Based on the dates in K1 & K2, I would like to have a formula in K4 to show me the headcount as of "today", and in K5 it will show the headcount as of the end of the previous month.
To headcount will be determined by columns "G" & "H".
For the headcount today calculation = count those employees with both a hire date EQUAL to or EARLIER than K2 AND a termination date GREATER than or EQUAL to K2.
For the headcount P.R.D. calculation = count those employees with both a hire date EQUAL to or EARLIER than K3 AND a termination date GREATER than or EQUAL to K3.
Where the termination date is blank, the employee is counted as still working with the company and should be included in the company.
Disregard all the other fields in the dataset, only "Hire Date" & "Termination Date" are being used for this calculation.
My data set begins in A1. Sample below.
Thank you.
Untitled Spreadsheet
Based on the dates in K1 & K2, I would like to have a formula in K4 to show me the headcount as of "today", and in K5 it will show the headcount as of the end of the previous month.
To headcount will be determined by columns "G" & "H".
For the headcount today calculation = count those employees with both a hire date EQUAL to or EARLIER than K2 AND a termination date GREATER than or EQUAL to K2.
For the headcount P.R.D. calculation = count those employees with both a hire date EQUAL to or EARLIER than K3 AND a termination date GREATER than or EQUAL to K3.
Where the termination date is blank, the employee is counted as still working with the company and should be included in the company.
Disregard all the other fields in the dataset, only "Hire Date" & "Termination Date" are being used for this calculation.
My data set begins in A1. Sample below.
Thank you.
Untitled Spreadsheet
Employee # | First name Last name | Status | Gender | Birth Date | Country | Hire Date | Termination Date | Today's Date | 09/06/2021 | |
1 | Empl X1 | Inactive | Female | 03/12/1985 | Viet Nam | 01/06/2017 | 30/09/2020 | Previous Reporting Day | 31/05/2021 | |
2 | Empl X2 | Active | Male | 27/02/1981 | Nigeria | 04/08/2010 | ||||
3 | Empl X3 | Active | Male | 13/06/1983 | Armenia | 04/04/2016 | Headcount - Today | |||
4 | Empl X4 | Inactive | Male | 31/05/1983 | Chile | 12/06/2017 | 14/08/2020 | Headcount - P.R.D | ||
5 | Empl X5 | Active | Female | 11/04/1988 | Armenia | 01/08/2016 | 01/06/2019 | |||
6 | Empl X6 | Active | Male | 24/12/1982 | Nigeria | 06/08/2020 | ||||
7 | Empl X7 | Active | Male | 30/06/1981 | Nigeria | 03/01/2011 | 06/07/2019 | |||
8 | Empl X8 | Active | Male | 08/12/1980 | Kenya | 01/06/2020 | ||||
9 | Empl X9 | Active | Male | 28/01/1994 | Armenia | 22/04/2021 | ||||
10 | Empl X10 | Active | Male | 26/09/1989 | Armenia | 01/02/2021 | ||||
11 | Empl X11 | Inactive | Female | 14/04/1982 | Armenia | 15/07/2019 | 16/06/2020 | |||
12 | Empl X12 | Inactive | Male | 15/04/1982 | Kenya | 14/05/2016 | 26/08/2020 | |||
13 | Empl X13 | Inactive | Male | 16/04/1982 | Kenya | 31/01/2011 | ||||
14 | Empl X14 | Active | Male | 17/04/1982 | Armenia | 03/06/2016 | ||||
15 | Empl X15 | Active | Female | 18/04/1982 | Armenia | 07/08/2020 | 04/06/2021 | |||
16 | Empl X16 | Active | Female | 19/04/1982 | Chile | 04/08/2020 | ||||
17 | Empl X17 | Inactive | Male | 20/04/1982 | Chile | 06/11/2021 | ||||
18 | Empl X18 | Inactive | Female | 21/04/1982 | Armenia | 07/09/2021 | ||||
19 | Empl X19 | Active | Female | 22/04/1982 | Nigeria | 05/06/2002 | 07/08/2018 | |||
20 | Empl X20 | Inactive | Female | 23/04/1982 | Nigeria | 05/07/2002 |