Eligibility Date formula

[runeyjam]

I have to put together a spreadsheet that will show the date a person is eligible to receive 401K benefits at our company. The criteria are that you are eligible 6 months after your hire date. However, you can only start your eligibility on the first day of a new quarter after your 6 months are up. If you were hired by or before 7/1/2002 you are, of course, eligible by 1/1/03. I have provided some examples below. How can I set up a formula in column C, to automatically calculate the eligibility date for any date I enter in column B.

Date eligibility 401k.xls
	A	B	C	D	E
1	Eligibility Dates
2	Year 2003	1/1/2003	4/1/2003	7/1/2003	10/1/2003
3	Year 2004	1/1/2004	4/1/2004	7/1/2004	10/1/2004
4
5	First Name	Date of Hire	Eligibility
6	Basmattie	7/23/2001	1/1/2003
7	Richard	9/16/1991	1/1/2003
8	Robin	5/5/2003	1/1/2004
9	Greg	4/1/2003	10/1/2003
10	Alan	4/8/2003	1/1/2004
11	Georgette	2/2/2003	10/1/2003
12	Bob	11/11/2003	7/1/2004
13	Harold	9/25/2003	4/1/2004
14	James	11/11/2002	7/1/2003
15	Skip	7/1/2002	1/1/2003
16	Chrissy	7/5/2002	4/1/2003
17	Lance	3/14/2003	10/1/2003
Sheet1

 

[Zack Barresse]

have you tried an if statement with a vlookup or hlookup?

 

[earlyd]

There may be a simpler way with other date functions, but this little formula might do the job:

=IF(DAY(D9)=1,IF(MONTH(D9)=1,DATE(YEAR(D9),7,1),IF(MONTH(D9)<5,DATE(YEAR(D9),10,1),IF(MONTH(D9)<8,DATE(YEAR(D9)+1,1,1),IF(MONTH(D9)<11,DATE(YEAR(D9)+1,4,1),DATE(YEAR(D9)+1,7,1))))),IF(MONTH(D9)<4,DATE(YEAR(D9),10,1),IF(MONTH(D9)<7,DATE(YEAR(D9)+1,1,1),IF(MONTH(D9)<10,DATE(YEAR(D9)+1,4,1),DATE(YEAR(D9),7,1)))))

 

[fairwinds]

Hi,

Try:

=DATE(YEAR(B6+1),INDEX({19,16,13,10},MATCH(MONTH(B6+1)-1,{12,9,6,3},-1)),1)

in C6 and drag down.

Does it give you what you want?

 

[earlyd]

Note - the formula I sent you does not have a minimum answer - you might need a min function around it with your earliest possible date of 1/1/2003 if that's the case.

 

[earlyd]

One more comment - the tricky part of this one seems to be that the first of the month will result in a different answer than the other days; i.e. while 4/1/04 will result in 10/1/04, 4/2/04 results in 1/1/05 if I understood you correctly.

 

[rrdonutz]

This formula should work:

=MAX(DATE(2003,1,1),DATE(YEAR(B6),10+3*INT((MONTH(B6-1)-1)/3),1))

unless the hire date is the first of the year (Jan. 1). Haven't tweaked it for that possibility, but then, not too many HR departments are open on Jan. 1 anyway. Other first of quarter dates (such as April 1, July 1, and October 1) work with this formula.

--Tom

 

[fairwinds]

... and this is a correction to my formula posted above

=DATE(YEAR(B6-1),INDEX({19,16,13,10},MATCH(MONTH(B6-1),{12,9,6,3},-1)),1)

 

[rrdonutz]

Okay, this "tweaking" should work:

MrE1204.xls
	A	B	C	D
5	Name	DateofHire	Eligibility
6	Basmattie	7/23/01	1/1/03
7	Richard	9/16/91	1/1/03
8	Robin	5/5/03	1/1/04
9	Greg	4/1/03	10/1/03
10	Alan	4/8/03	1/1/04
11	Georgette	2/2/03	10/1/03
12	Bob	11/11/03	7/1/04
13	Harold	9/25/03	4/1/04
14	James	11/11/02	7/1/03
15	Skip	7/1/02	1/1/03
16	Chrissy	7/5/02	4/1/03
17	Lance	3/14/03	10/1/03
18	Rufus	1/1/04	7/1/04
19	Dufus	1/2/04	10/1/04
Sheet1

The formula in C6 is now:

=MAX(DATE(2003,1,1),DATE(YEAR(B6-1),10+3*INT((MONTH(B6-1)-1)/3),1))

 

[runeyjam]

Thanks guys. I appreciate it.