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
Name	Hire Date	Terminated	Status
Smith	Terminated
Jones	Ignore
Roberts	New Hire

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1/31/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12/31/2017[/TD]
[TD="align: right"]1/24/2018[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/10/2018[/TD]
[TD="align: right"]1/22/2018[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1/5/2018[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet293​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=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","")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.