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

 

[earlyd]

Glad someone found a shorter formula than my monstrosity!

 

[rrdonutz]

An even shorter one:

=MAX("1/1/03"+0,DATE(YEAR(B6-1),7+CEILING(MONTH(B6-1),3),1))

(No more tweaking from me, I promise.)