IF Statement for Employee Turnover Identification

[apontaza]

Hello,

I'm trying to determine if an employee is a new hire or terminated based on 3 criteria. I've tried creating a column concatenating the month and year to use an if(and statement but can't figure it out... I'd really appreciate any help or direction.

1. If hire month & year = Date in cell A1 then it's a New Hire
2. If employee is also terminated within same month & year ignore
3. If Termination date is blank = New Hire


A B C D
01/31/2018

Name Hire Date Terminated STATUS NEEDED
Smith 12/31/2017 01/24/2018 Terminated
Jones 01/10/2018 01/22/2018 Ignore
Roberts 01/05/2018 New Hire

 

[jtakw]

Hi,

Is this what you mean:

Book1
	A	B	C	D
1	1/31/2018
2
3	Name	Hire Date	Terminated	Status
4	Smith	12/31/2017	1/24/2018	Terminated
5	Jones	1/10/2018	1/22/2018	Ignore
6	Roberts	1/5/2018		New Hire
Sheet293

Cell Formulas
Range	Formula
D4	=IF(C4="","New Hire",IF(AND(EOMONTH(A$1,0)=EOMONTH(B4,0),EOMONTH(A$1,0)=EOMONTH(C4,0)),"Ignore",IF(EOMONTH(A$1,0)=EOMONTH(C4,0),"Terminated","")))

Formula copied down.

 

[apontaza]

Hi,

Is this what you mean:

	A	B	C	D
1	1/31/2018
2
3	Name	Hire Date	Terminated	Status
4	Smith	12/31/2017	1/24/2018	Terminated
5	Jones	1/10/2018	1/22/2018	Ignore
6	Roberts	1/5/2018		New Hire

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet293​

Worksheet Formulas Cell Formula D4 =IF(C4="","New Hire",IF(AND(EOMONTH(A$1,0)=EOMONTH(B4,0),EOMONTH(A$1,0)=EOMONTH(C4,0)),"Ignore",IF(EOMONTH(A$1,0)=EOMONTH(C4,0),"Terminated",""))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Formula copied down.

THANK YOU!!!! This works.

 

[jtakw]

You're welcome.

Just to be sure, what if "Hire Date" is say, 10/1/2017, but "Terminated" is Blank, is it Still a "New Hire" or something else?

 

[apontaza]

That scenario shouldn't appear, our Payroll service ADP has specifically made the report with this data in mind. Thank you though.

 

[jtakw]

Ok, then the formula should work fine as is.