# 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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### 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
9
Views
126
Replies
12
Views
780
Replies
1
Views
4K
Replies
4
Views
281
Replies
5
Views
1K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,102
Messages
5,768,102
Members
425,454
Latest member
khoro

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

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