Count number of employees employed between two dates

[nathangwynmorris]

Hi All,

Newbie poster, but have used the forum a number of times! This question has been asked and answered before, but I really cannot get it to work [count of employees which were employed between two dates].

I have a list of start dates (A:A) and end dates (B:B) of employees who were employed in the company. If the employee is still employed the end date (B:B) will be blank. What I would like to do is count if the employee was employed between two dates.

In D1:12, I have the start date of the period, in E1:12 the end date of period [E.G D1 = 31/01/2021, E1 = 28/02/2021]. In F1 I would like it to display how many people were employed in the list for that period. in F2 the next period etc.

Please help! Mini sheet screenshot attached. Thanks in advance

Nathan

 

[johnny51981]

Here ya go:

=COUNTIFS(A:A,"<>"&"",B:B,"")+COUNTIFS(A:A,"<>"&"",B:B,">="&D1,B:B,"<="&E1)

It first counts those where the Start Date is not null, and the Leave Date is null.
It then adds that to the count of those where the Start Date is not null, the Leave Date is greater or equal to the Period Start and less than or equal to the Period End.

 

[nathangwynmorris]

Hey @johnny51981,

Thanks so much for coming back so quickly. Unfortunately that isn't working. If someone wasn't employed in that period they still have a start date in the future if that makes sense. So in the new example I have put up, I have also given what my expected results are.

Cheers,

Nathan

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS(A:A,"<="&E2,B:B,"")+COUNTIFS(A:A,"<="&E2,B:B,">="&D2)

 

[nathangwynmorris]

@Fluff - thank you so so much! Yes, I think that does it!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[nathangwynmorris]

@Fluff - Sorry to open this back up and apologies if it should be in another post. But one final question on this.

To validate the count, what formula would I use if I wanted to place a 1 next to the row that was in that period? For example, in the screenshot above in C3, I want to place a 1 if that date range is in between D2 & E2. I just want to do a quick validation that is all.

 

[Fluff]

How about

=--(AND(A3<$E$2,OR(B3="",B3>$D$2)))