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

[GayathriSK]

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]

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.

[#3]

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)