CODEWITHSUNDEEP
excelexcel-formula
byrak79
byrak79

Reputation: 61

Active Employees by Month

I have the following data

Hire Date      Termination Date
1/28/2019             4/16/2019     
2/18/2020             
9/17/2018             3/12/2020

I need to find how many active employees the company had for each month. So far, I tried the formula below, and it came pretty close. But I need accurate numbers.

{=SUM((IF('Employee Roster'!C$2:C$5000<>"",'Employee Roster'!C$2:C$5000,"")<=EOMONTH(A38,0))*(IF('Employee Roster'!D$2:D$5000="",1E+100,'Employee Roster'!D$2:D$5000)>EOMONTH(A38,0)))}

For the formula, Employee Roster C is the hire date, A38 is the Month column in my final table, Employee Roster D is the Termination Date.

At the end, I wish my final table to look like below

Month             Active Employees
February 2019           100
March 2019              129
April 2019              150
...

Upvotes: 1

Views: 1220

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

  • Construct a list of all the month start dates of interest.
    • In O365, you can use the formula =EDATE(EOMONTH(MIN(Hires),-1)+1,SEQUENCE(DATEDIF(EOMONTH(MIN(Hires),-1)+1,EOMONTH(TODAY(),0),"m")+1)-1)
    • In other versions, you could construct a list of months manually.

and it will SPILL down as far as required. But there are other ways to create this list, even manually.

  • In the adjacent cell to the first month, enter the formula:

    =SUMPRODUCT((IF(Termination="",TODAY(),Termination)>=A2)*(Hires<=EOMONTH(A2,0)))

and fill down as far as required.

Algorithm

If a person is an employee during a given month, then

  • His termination date must be after or equal to the start of that month
    • If there is no termination date, it is set to TODAY()
  • His hire date must be equal to or prior the end of that month

In the screenshot, I formatted the month as m/d/yyy so as to emphasize it needed to be the first of the month; but you can format it as mmmm yyy to match your desired report requirement

enter image description here

Upvotes: 1

Related Questions