Comparing Dates and Identifying Employees

K

kgun12

New Member
Joined
Dec 24, 2016
Messages
12
[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?
 
Sort by date Sort by votes
kgun12 said:
Thanks Marty.
Click to expand...


Always a pleasure....

Not in the office right now. Will report back soon.
Click to expand...

No worries...

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

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
ABCDE
N : Did not start in 2017
N : Did not start in 2017
Y : Separated>90
N :Separated<90
N : Did not start in 2017
Y : Separated>90
Y : Still Emp and >90
Y : Still Emp and >90
Y : Separated>90
Test Name 1N : Did not start in 2017
Test Name 2N : Still Emp < 90d
Test Name 3N : Still Emp < 90d
Test Name 4Y : Still Emp and >90
Test Name 5N : Did not start in 2017
Test Name 6N :Separated<90
Test Name 7N :Separated<90
Test Name 8Y : Separated>90
Test Name 9N : Did not start in 2017
Test Name 10N : Did not start in 2017
Test Name 11N : Did not start in 2017
Test Name 12N : Did not start in 2017
Test Name 13N : Did not start in 2017
Test Name 14N : Did not start in 2017
Test Name 15N : Did not start in 2017
Test Name 16N : SepDate<nmdate< td=""></nmdate<>

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Hire Date[/TD]
[TD="align: center"]Separation Date[/TD]
[TD="align: center"]Qualify[/TD]
[TD="align: center"]Qualify with Reason[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Agustin[/TD]
[TD="align: center"]16/07/2014[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Aldo[/TD]
[TD="align: center"]17/03/2014[/TD]
[TD="align: center"]21/01/2018[/TD]
[TD="align: center"]N[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Ibrahim[/TD]
[TD="align: center"]21/01/2017[/TD]
[TD="align: center"]07/01/2018[/TD]
[TD="align: center"]Y[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Luis[/TD]
[TD="align: center"]02/08/2017[/TD]
[TD="align: center"]03/09/2017[/TD]
[TD="align: center"]N[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Issaha[/TD]
[TD="align: center"]28/09/2016[/TD]
[TD="align: center"]20/11/2016[/TD]
[TD="align: center"]N[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]01/02/2017[/TD]
[TD="align: center"]05/09/2017[/TD]
[TD="align: center"]Y[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]Arton[/TD]
[TD="align: center"]17/05/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]

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

[TD="align: center"]10[/TD]
[TD="align: center"]Ibrahim[/TD]
[TD="align: center"]21/01/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Ibrahim[/TD]
[TD="align: center"]21/01/2017[/TD]
[TD="align: center"]07/01/2018[/TD]
[TD="align: center"]Y[/TD]

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

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Hire Date[/TD]
[TD="align: center"]Separation Date[/TD]
[TD="align: center"]Qualify[/TD]
[TD="align: center"]Qualify with Reason[/TD]

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

[TD="align: center"]01/01/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]15/12/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]12/11/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]05/10/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y[/TD]

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

[TD="align: center"]01/01/2018[/TD]
[TD="align: center"]30/01/2018[/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]15/12/2017[/TD]
[TD="align: center"]31/01/2018[/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]12/11/2017[/TD]
[TD="align: center"]01/02/2018[/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]05/10/2017[/TD]
[TD="align: center"]02/02/2018[/TD]
[TD="align: center"]Y[/TD]

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

[TD="align: center"]01/01/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]15/12/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]12/11/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]05/10/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]01/01/2016[/TD]
[TD="align: center"]30/01/2018[/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]15/12/2016[/TD]
[TD="align: center"]31/01/2018[/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]12/11/2016[/TD]
[TD="align: center"]01/02/2018[/TD]
[TD="align: center"]N[/TD]

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

[TD="align: center"]01/01/2017[/TD]
[TD="align: center"]30/01/2017[/TD]
[TD="align: center"]N[/TD]

</tbody>
FINAL​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=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"),"")[/TD]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=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"),"")[/TD]
[/TR]
[TR]
[TH][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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

Code: 
=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"),"")
 
Last edited:
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
MartyS said:
This might help.

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


(you'll want the third sheet called FINAL)
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
552
mrshuck
M
G
  • Question Question
Employee Head Count Pivot Table
Replies
0
Views
1K
goob90
G
G
  • Question Question
Headcount Using Hire Date & Term Date Power Query
Replies
0
Views
668
goob90
G
A
  • Question Question
Lookup+sum+max
Replies
2
Views
548
aarky
A
Z
  • Question Question
Attrition forecast for next 3 months?
Replies
0
Views
512
zinah
Z

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top