Count number of employees employed between two dates

nathangwynmorris

New Member
Joined
Feb 23, 2022
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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
 

Attachments

  • mrexcel.PNG
    mrexcel.PNG
    37.5 KB · Views: 62

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

johnny51981

Board Regular
Joined
Jun 8, 2015
Messages
214
Here ya go:
Excel Formula:
=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

New Member
Joined
Feb 23, 2022
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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
 

Attachments

  • mrexcel2.PNG
    mrexcel2.PNG
    22.7 KB · Views: 70

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=COUNTIFS(A:A,"<="&E2,B:B,"")+COUNTIFS(A:A,"<="&E2,B:B,">="&D2)
 
Solution

nathangwynmorris

New Member
Joined
Feb 23, 2022
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
@Fluff - thank you so so much! Yes, I think that does it!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

nathangwynmorris

New Member
Joined
Feb 23, 2022
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
@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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,703
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=--(AND(A3<$E$2,OR(B3="",B3>$D$2)))
 

Forum statistics

Threads
1,176,085
Messages
5,901,298
Members
434,886
Latest member
qazibelal

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
Top