How to calculate employee headcount by department over a period of time with transfers

[mhaider99]

I have managed to calculate the headcount and actual number of staff by department over a period of time using starting date and leaving date using a calendar table. However, now i need to re-do the analysis which should also take into consideration the headcount of employees that have transfered to another department.

Headcount will be the aggregate of new joiners

Active Staff will be the Head count - leavers - transfers

Lets say the table is Employees and has the following fields:

a) Employee ID

b) Date of joining

c) Departmend joined

d) Transfer date

e) Department transfered to

f) Leaving Date

If employee A joined in January 2022, Department Finance, then the headcount of Department Finance should be incremented by 1 and active staff value should be incremented;

If the employee A transfers to another department (Procurement) in August 2022 then the headcount of Department Finance should not be impact but the active staff should decreased by 1 and the headcount and active staff of department Procurement should be incremented by 1.

In summary, need to monitor the headcount and active staff by department as changes to joiners and transfers and leavers happen over a period of time.

Any help would be really appreciated. 

[AkameNoGamma]

I also need this to meausure the headcount of each department within a fiscal year (april to march). most of the codes that i see referring to the date of join and separation date of an employee, and those formulas work as long as you are looking at overall headcount and attrition of your company.

when it comes to measure at the department level, you will need an opening headcount of each departments based on their hire date, separation date, and effective date of transfer, the the result must be the same with your overall headcount.

i dont have the formula right bow but what i am thinking is to merge the files into one. like all hires, separation and transfer. (headers: employee ID, effective date, Status (which shows hire date, separation date, and transfer date of the employee), and last is department (this will show the employee's first department, the department he got transffered to, and the last department before he got separated)

after this, i just need the formula measure this. so if i need to get the opening headcount of April 1, 2023, then all active employees as of march 30 will show up, which is based on their status and department.