Forum Discussion
I am trying to find active employee headcount with multiple hire dates and termination dates
I have a dataset as below to find the count of employees.
Possible scenario: The employee has joined as contractor and was terminated. Then Emp A is rehired as Full time.
Expected Headcount for the month of 01/31/2020: Emp A Contractor and Emp B
Can anybody help formulating the logic to calculate monthly headcount?
| EMPLID | ACTION_DT | Effective Date | Record Date | Record Type | PS_Hire Date (from Legacy System) | WD_Hire_Date | WD_Original_Hire_Date | WD_Termination date | WD_Contract_End_Date |
| A (Full time) | 4/2/2019 | 4/8/2019 | 1/31/2020 | Snapshot | 4/8/2019 | 4/6/2021 | 4/6/2021 | ||
| B | 10/9/2019 | 10/15/2019 | 1/31/2020 | Snapshot | 10/15/2019 | 4/13/2020 | 4/13/2020 | 6/30/2020 | 6/30/2020 |
| A Contractor (Contract Ended) | 4/2/2019 | 4/8/2019 | 1/31/2020 | Snapshot | 4/8/2019 | 4/8/2019 | 4/8/2019 | 4/4/2021 | 4/4/2021 |
2 Replies
Hello GayathriSK,
Perhaps this is what you're after?
=SUM(
COUNTIFS($I$2:$I$4,">"&TODAY(),$H$2:$H$4,"<="&TODAY()),
COUNTIFS($I$2:$I$4,"",$H$2:$H$4,"<="&TODAY())
)Where WD_Termination date = $I$2:$I$4 and WD_Original_Hire_Date = $H$2:$H$4.
- Active Employee Case 1:
COUNTIFS($I$2:$I$4,">"&TODAY(),$H$2:$H$4,"<="&TODAY()) — Counts if term date is after today and hire date is on or before today. - Active Employee Case 2:
COUNTIFS($I$2:$I$4,"",$H$2:$H$4,"<="&TODAY()) — Counts if term date is blank and hire date is on or before today.
- as you can see in my first column, the EMP A has been hired as contractor. The contract starts on 4/8/2019 and ends on 4/4/2021. Then the emp is converted to Fulltime on 04/06/2021.
So the solution has to check if the person was hired in the legacy system and also in the current HR system(Workday). Once done, it should link the record to the right employee profile(contractor or fulltime)
- Active Employee Case 1:
