# Eligibility Date formula

#### runeyjam

##### Board Regular
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
ABCDE
1Eligibility Dates
2Year 20031/1/20034/1/20037/1/200310/1/2003
3Year 20041/1/20044/1/20047/1/200410/1/2004
4
5First NameDate of HireEligibility
6Basmattie7/23/20011/1/2003
7Richard9/16/19911/1/2003
8Robin5/5/20031/1/2004
9Greg4/1/200310/1/2003
10Alan4/8/20031/1/2004
11Georgette2/2/200310/1/2003
12Bob11/11/20037/1/2004
13Harold9/25/20034/1/2004
14James11/11/20027/1/2003
15Skip7/1/20021/1/2003
16Chrissy7/5/20024/1/2003
17Lance3/14/200310/1/2003
Sheet1

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Zack Barresse

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

#### earlyd

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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

##### MrExcel MVP
... 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

##### Well-known Member
Okay, this "tweaking" should work:
MrE1204.xls
ABCD
5NameDateofHireEligibility
6Basmattie7/23/011/1/03
7Richard9/16/911/1/03
8Robin5/5/031/1/04
9Greg4/1/0310/1/03
10Alan4/8/031/1/04
11Georgette2/2/0310/1/03
12Bob11/11/037/1/04
13Harold9/25/034/1/04
14James11/11/027/1/03
15Skip7/1/021/1/03
16Chrissy7/5/024/1/03
17Lance3/14/0310/1/03
18Rufus1/1/047/1/04
19Dufus1/2/0410/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

##### Board Regular
Thanks guys. I appreciate it.

Replies
3
Views
257
Replies
28
Views
782
Replies
9
Views
226
Replies
12
Views
907
Replies
1
Views
5K

1,186,924
Messages
5,960,584
Members
438,486
Latest member
ncc84330

### 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.

### Which adblocker are you using?

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

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