New Contributor

# 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

# Re: I am trying to find active employee headcount with multiple hire dates and termination dates

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.

# Re: I am trying to find active employee headcount with multiple hire dates and termination dates

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)