Eligibility Date formula

runeyjam

Board Regular
Joined
Feb 10, 2003
Messages
75
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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
have you tried an if statement with a vlookup or hlookup?
 

earlyd

Well-known Member
Joined
Dec 10, 2002
Messages
1,199
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
Joined
May 15, 2003
Messages
8,638
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
Joined
Dec 10, 2002
Messages
1,199

ADVERTISEMENT

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
Joined
Dec 10, 2002
Messages
1,199
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
Joined
Jan 15, 2003
Messages
564

ADVERTISEMENT

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
Joined
May 15, 2003
Messages
8,638
... and this is a correction to my formula posted above :oops:

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

rrdonutz

Well-known Member
Joined
Jan 15, 2003
Messages
564
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))
 
Master Excel Bundle

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.

Forum statistics

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

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
Top