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

New Contributor

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?

 

 

EMPLIDACTION_DTEffective DateRecord DateRecord TypePS_Hire Date (from Legacy System)WD_Hire_DateWD_Original_Hire_DateWD_Termination dateWD_Contract_End_Date
A (Full time)4/2/20194/8/20191/31/2020Snapshot4/8/20194/6/20214/6/2021  
B10/9/201910/15/20191/31/2020Snapshot10/15/20194/13/20204/13/20206/30/20206/30/2020
A Contractor (Contract Ended)4/2/20194/8/20191/31/2020Snapshot4/8/20194/8/20194/8/20194/4/20214/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)