Count of active employees between dates.

[surkdidat]

I am trying to work out the formula so I can count the number of employees that have started on or before the start date in column B, and have left on or after (or still employed, so blank) in column C for every month from June 2023 to June 2024 inclusive.

EmployeeID	Hire Date	Termination Date
Employee1	06/02/2023
Employee2	03/01/2023
Employee3	03/07/2023
Employee4	10/06/2024
Employee5	03/10/2022	19/10/2022
Employee6	04/11/2020	05/08/2022
Employee7	01/09/2022	09/05/2024
Employee8	10/10/2022	11/08/2023
Employee9	08/06/2022	22/12/2023
Employee10	04/07/2022	11/09/2023

So for June 2023, 6 employees (Employees, 1 2 7 8 9 & 10) started on or before 1 June 2023, and were active on 30 June 2023 or after.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	EmployeeID	Hire Date	Termination Date
2	Employee1	06/02/2023			01/06/2023	6
3	Employee2	03/01/2023			01/07/2023	6
4	Employee3	03/07/2023			01/08/2023	6
5	Employee4	10/06/2024
6	Employee5	03/10/2022	19/10/2022
7	Employee6	04/11/2020	05/08/2022
8	Employee7	01/09/2022	09/05/2024
9	Employee8	10/10/2022	11/08/2023
10	Employee9	08/06/2022	22/12/2023
11	Employee10	04/07/2022	11/09/2023
Data

Cell Formulas
Range	Formula
F2:F4	F2	=COUNTIFS(B:B,"<="&E2,C:C,">="&EOMONTH(E2,0))+COUNTIFS(B:B,"<="&E2,C:C,"")

 

[surkdidat]

Thanks as ever Fluff, works perfectly!

 

[Fluff]

You're welcome & thanks for the feedback.