Comparing Dates and Identifying Employees

[kgun12]

[TABLE="width: 303"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Hire Date[/TD]
[TD]Separation Date[/TD]
[TD]Qualify[/TD]
[/TR]
[TR]
[TD]Agustin[/TD]
[TD="align: right"]7/16/2014[/TD]
[TD][/TD]
[TD]Num[/TD]
[/TR]
[TR]
[TD]Aldo[/TD]
[TD="align: right"]3/17/2014[/TD]
[TD="align: right"]1/21/2018[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Ibrahim[/TD]
[TD="align: right"]1/21/2017[/TD]
[TD="align: right"]1/7/2018[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Luis[/TD]
[TD="align: right"]8/2/2017[/TD]
[TD="align: right"]9/3/2017[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Issaha[/TD]
[TD="align: right"]9/28/2016[/TD]
[TD="align: right"]11/20/2016[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]2/1/2017[/TD]
[TD="align: right"]9/5/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Arton[/TD]
[TD="align: right"]5/17/2017[/TD]
[TD][/TD]
[TD]Num[/TD]
[/TR]
</tbody>[/TABLE]

Hope someone can point me in the right direction. I'm a relative novice with excel but I'm better than the next guy at work so I've been given the simple task of creating a list of employees hired in 2017 that were employed with the firm for more than 90 days and would qualify for certain benefits. Above is a small list taken from a much larger sheet. I've inserted the formula:[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]IF(AND(ISNUMBER(C2),B2>=DATE(2017,1,1),DATEDIF(EOMONTH(B2,0)+1,C2,"d")>90),"Yes","No")[/TD]
[/TR]
</tbody>[/TABLE]

under the "Qualify" column, it works except when the employee does not have a separation date since they're still employeed with the firm, my formula kicks out a #NUM message. And advice?

 

[Special-K99]

This is what I'd call a workaround.

=IFERROR(IF(AND(ISNUMBER(C2),B2>=DATE(2017,1,1),DATEDIF(EOMONTH(B2,0)+1,C2,"d")>90),"Yes","No")"),"No")

If an error is produced return "No"


From a programming point of view I prefer this

=IF(C2>0,IF(AND(B2>=DATE(2017,1,1),DATEDIF(EOMONTH(B2,0)+1,C2,"d")>90),"Yes","No"),"No")

 

[MartyS]

[TABLE="width: 303"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Hire Date[/TD]
[TD]Separation Date[/TD]
[TD]Qualify[/TD]
[/TR]
[TR]
[TD]Agustin[/TD]
[TD="align: right"]7/16/2014[/TD]
[TD][/TD]
[TD]Num[/TD]
[/TR]
[TR]
[TD]Aldo[/TD]
[TD="align: right"]3/17/2014[/TD]
[TD="align: right"]1/21/2018[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Ibrahim[/TD]
[TD="align: right"]1/21/2017[/TD]
[TD="align: right"]1/7/2018[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Luis[/TD]
[TD="align: right"]8/2/2017[/TD]
[TD="align: right"]9/3/2017[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Issaha[/TD]
[TD="align: right"]9/28/2016[/TD]
[TD="align: right"]11/20/2016[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]2/1/2017[/TD]
[TD="align: right"]9/5/2017[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Arton[/TD]
[TD="align: right"]5/17/2017[/TD]
[TD][/TD]
[TD]Num[/TD]
[/TR]
</tbody>[/TABLE]

Hope someone can point me in the right direction. I'm a relative novice with excel but I'm better than the next guy at work so I've been given the simple task of creating a list of employees hired in 2017 that were employed with the firm for more than 90 days and would qualify for certain benefits. Above is a small list taken from a much larger sheet. I've inserted the formula:[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]IF(AND(ISNUMBER(C2),B2>=DATE(2017,1,1),DATEDIF(EOMONTH(B2,0)+1,C2,"d")>90),"Yes","No")[/TD]
[/TR]
</tbody>[/TABLE]

under the "Qualify" column, it works except when the employee does not have a separation date since they're still employeed with the firm, my formula kicks out a #NUM message. And advice?

Could I query your calculation for the > 90 days?



Currently you are calculating: [SEPARATION DATE] - [1ST OF NEXT MONTH AFTER HIRE DATE]


that means: IF someone started on, say, 15th JAN... your calculation uses 1st FEB... potentially losing around 15 days they've been with the company. Is this correct?



Also, are these benefits for people that have left the company? Or benefits for people who are still employed at the company??


Currently you are calculating:

Those that have LEFT the company... who have more than 90 days FROM THE FIRST OF THE NEXT MONTH after they joined - these are the people that qualify.



Is this correct?

 

[kgun12]

Could I query your calculation for the > 90 days?



Currently you are calculating: [SEPARATION DATE] - [1ST OF NEXT MONTH AFTER HIRE DATE]


that means: IF someone started on, say, 15th JAN... your calculation uses 1st FEB... potentially losing around 15 days they've been with the company. Is this correct?



Also, are these benefits for people that have left the company? Or benefits for people who are still employed at the company??


Currently you are calculating:

Those that have LEFT the company... who have more than 90 days FROM THE FIRST OF THE NEXT MONTH after they joined - these are the people that qualify.



Is this correct?

The calculation should begin with the next month after hire, so if someone was hired on the 15th of January, benefit would starting calculating with the 1st of February.

I want to calculate anyone who was hired in 2017, and worked more than 90 days with the first of the next month after they were hired. If for example Ibrahim was not terminated, which would mean no separation date, he should still qualify. With the current setup it would not give Ibrahim a "YES" for benefits if he had no separation. Does this clarify things?

Appreciate the help, thanks to Special-K99.

 

[MartyS]

If for example Ibrahim was not terminated, which would mean no separation date, he should still qualify.

So it doesn't matter whether someone has a separation date or not, if they started in 2017 and they're over 90 days from the first day of the next month of hiring, they Qualify?

 

[kgun12]

So it doesn't matter whether someone has a separation date or not, if they started in 2017 and they're over 90 days from the first day of the next month of hiring, they Qualify?

Yes, exactly.

 

[MartyS]

I want to calculate anyone who was hired in 2017.

Is it JUST

calculate for [anyone hired IN 2017]

OR

calculate for [anyone hired IN 2017 AND BEYOND]?



I ask because your formula is set to:

[anyone hired IN 2017 AND BEYOND]

and your description of the criteria is:

I want to calculate anyone who was hired IN 2017.

 

[kgun12]

Is it JUST

calculate for [anyone hired IN 2017]

OR

calculate for [anyone hired IN 2017 AND BEYOND]?



I ask because your formula is set to:

[anyone hired IN 2017 AND BEYOND]

and your description of the criteria is:

Im just worried about 2017 right now.

 

[MartyS]

Im just worried about 2017 right now.

The formula you're looking for is relatively complex (insofar as there are a number of considerations you have to include). It is not as simple as it looks!


If you are ONLY interested in those that were hired in 2017 (and thus EXCLUDE anyone before 2017 and anyone after 2017), at the very least your formula needs to include:

B2<=DATE(2017,12,31)

so it doesn't go beyond 2017



With what you have already (which is nowhere near what you need, yet) that would give:

=IF(C2>0,IF(AND(B2>=DATE(2017,1,1),B2<=DATE(2017,12,31), DATEDIF(EOMONTH(B2,0)+1,C2,"d")>90),"Yes","No"),"No")

 

[kgun12]

The formula you're looking for is relatively complex (insofar as there are a number of considerations you have to include). It is not as simple as it looks!


If you are ONLY interested in those that were hired in 2017 (and thus EXCLUDE anyone before 2017 and anyone after 2017), at the very least your formula needs to include:




so it doesn't go beyond 2017



With what you have already (which is nowhere near what you need, yet) that would give:

Thanks Marty. Not in the office right now. Will report back soon. At least I'm not crazy thinking this is a tough one to crack. Last night I had it it calculating pretty well, but anyone before 2017 was labeled as False.