Comparing Dates and Identifying Employees

[kgun12]

Name	Hire Date	Separation Date	Qualify
Agustin	7/16/2014		Num
Aldo	3/17/2014	1/21/2018	No
Ibrahim	1/21/2017	1/7/2018	No
Luis	8/2/2017	9/3/2017	No
Issaha	9/28/2016	11/20/2016	No
John	2/1/2017	9/5/2017	Yes
Arton	5/17/2017		Num

<tbody>
</tbody>

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:

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

<tbody>
</tbody>

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?

 

[MartyS]

Thanks Marty.

Always a pleasure....

Not in the office right now. Will report back soon.

No worries...

At least I'm not crazy thinking this is a tough one to crack.

I actually had to draw this one out on a sheet of paper and then put it together....




NB there are TWO formulae here (not both are necessary).


The first one just gives you a Y or an N for whether they qualify or not.

The second does exactly the same thing BUT... it gives you the REASON as well (as you can see in the table below)



Below is your table (date format rearranged for UK dates = DD-MM-YYYY)...

PLUS

some test data that should cover all criteria / variations / error trapping.

I even added in a check to "only do this calculation if there is a HIRE DATE in the first place"...!


Let me know how you get on...




Excel 2010

	A	B	C	D	E
1	Name	Hire Date	Separation Date	Qualify	Qualify with Reason
2	Agustin	16/07/2014		N	N : Did not start in 2017
3	Aldo	17/03/2014	21/01/2018	N	N : Did not start in 2017
4	Ibrahim	21/01/2017	07/01/2018	Y	Y : Separated>90
5	Luis	02/08/2017	03/09/2017	N	N :Separated<90
6	Issaha	28/09/2016	20/11/2016	N	N : Did not start in 2017
7	John	01/02/2017	05/09/2017	Y	Y : Separated>90
8	Arton	17/05/2017		Y	Y : Still Emp and >90
9
10	Ibrahim	21/01/2017		Y	Y : Still Emp and >90
11	Ibrahim	21/01/2017	07/01/2018	Y	Y : Separated>90
12
13		Hire Date	Separation Date	Qualify	Qualify with Reason
14	Test Name 1	01/01/2018		N	N : Did not start in 2017
15	Test Name 2	15/12/2017		N	N : Still Emp < 90d
16	Test Name 3	12/11/2017		N	N : Still Emp < 90d
17	Test Name 4	05/10/2017		Y	Y : Still Emp and >90
18	Test Name 5	01/01/2018	30/01/2018	N	N : Did not start in 2017
19	Test Name 6	15/12/2017	31/01/2018	N	N :Separated<90
20	Test Name 7	12/11/2017	01/02/2018	N	N :Separated<90
21	Test Name 8	05/10/2017	02/02/2018	Y	Y : Separated>90
22	Test Name 9	01/01/2016		N	N : Did not start in 2017
23	Test Name 10	15/12/2016		N	N : Did not start in 2017
24	Test Name 11	12/11/2016		N	N : Did not start in 2017
25	Test Name 12	05/10/2016		N	N : Did not start in 2017
26	Test Name 13	01/01/2016	30/01/2018	N	N : Did not start in 2017
27	Test Name 14	15/12/2016	31/01/2018	N	N : Did not start in 2017
28	Test Name 15	12/11/2016	01/02/2018	N	N : Did not start in 2017
29	Test Name 16	01/01/2017	30/01/2017	N	N : SepDate<nmdate< td=""></nmdate<>

<tbody>
</tbody>

FINAL​

Worksheet Formulas Cell Formula D2 =IF(<font color="Blue">B2>0,IF(<font color="Red">AND(B2>=DATE(2017,1,1),B2<=DATE(2017,12,31)),IF(C2>0,IF(C2<eomonth(B2,0DATEDIF(EOMONTH(B2,0)+1,C2,"d")>90,"Y","N"</eomonth()),IF(DATEDIF(EOMONTH(B2,0)+1,TODAY(),"d")>90,"Y","N")),"N"),"") E2 =IF(<font color="Blue">B2>0,IF(<font color="Red">AND(B2>=DATE(2017,1,1),B2<=DATE(2017,12,31)),IF(C2>0,IF(C2<eomonth(B2,0DATEDIF(EOMONTH(B2,0)+1,C2,"d")>90,"Y : Separated>90","N :Separated<90"),IF(DATEDIF(EOMONTH(B2,0)+1,TODAY(),"d")>90,"Y : Still Emp and >90","N : Still Emp < 90d")</eomonth(),"N : Did not start in 2017"),"") <tbody> </tbody>

<tbody>
</tbody>

Seems to be a problem with the HTML.. ignore the above formulae... its a formatting problem, not a formulae problem.

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


=IF(B2 > 0,IF(AND(B2 >= DATE(2017,1,1),B2 <= DATE(2017,12,31)),IF(C2 > 0,IF(C2 < EOMONTH(B2,0)+1,"N : SepDate < NMDate",IF(DATEDIF(EOMONTH(B2,0)+1,C2,"d") > 90,"Y : Separated > 90","N : Separated < 90")),IF(DATEDIF(EOMONTH(B2,0)+1,TODAY(),"d") > 90,"Y : Still Emp and  > 90","N : Still Emp  <  90d")),"N : Did not start in 2017"),"")

 

[MartyS]

This might help.

https://www.dropbox.com/s/ab7p3q70zc5d2r0/EOMonthQualifyProblem.xlsx?dl=0


(you'll want the third sheet called FINAL)

 

[kgun12]

This might help.

https://www.dropbox.com/s/ab7p3q70zc5d2r0/EOMonthQualifyProblem.xlsx?dl=0


(you'll want the third sheet called FINAL)

Marty, thank you so much.

This was what I wrote up last night:

IF(B2>DATE(2017,1,1),IF(ISNUMBER(C2),IF(DATEDIF(EOMONTH(B2,0)+1,C2,"D")>90,"YES","No"),IF(AND(B2>DATE(2017,1,1),DATEDIF(B2,TODAY(),"D")>90),"Yes","No")))

Anything before 2016 would pop up false, anything after 2017 would be #Num . Your solution does the trick, thanks again for putting in time to help me, greatly appreciate it.

 

[MartyS]

Looks like you were close..!

Glad it worked!