Eligibility Date formula 1 year service open enrollment Jan 1, Jul 1

[starryexcel]

I have searched and searched so I am now asking my first question.

I am looking for the formula to give me 1 year after date of hire, Jan 1 or Jul 1 whichever comes first to provided the date of open enrollment for 401K.

Cell A1 (Today's Date)
Cell J9 (Date of Hire)
Cell Q9 (Eligibility date, either 1/1 or 7/1, one year after cell J9)

Thank you for help "excel wonders of the world"...

 

[CyrusTheVirus]

Hi starryexcel,

Welcome to the forums. This is a bit long, but it might do the trick...
<date(year(j9)+1,6,1)),date(year(j9)+1,6,1),and(date(year(j9)+1,month(j9),day(j9))><date(year(j9)+2,1,1)))
<date(year(j9)+1,6,1)),date(year(j9)+1,6,1),and(date(year(j9)+1,month(j9),day(j9))><date(year(j9)+1,6,1)),date(year(j9)+1,6,1),and(date(year(j9)+1,month(j9),day(j9))><date(year(j9)+2,1,1)))

Pasting my formula seems to be giving me issues for some reason...</date(year(j9)+2,1,1)))
</date(year(j9)+1,6,1)),date(year(j9)+1,6,1),and(date(year(j9)+1,month(j9),day(j9))></date(year(j9)+1,6,1)),date(year(j9)+1,6,1),and(date(year(j9)+1,month(j9),day(j9))></date(year(j9)+2,1,1)))
</date(year(j9)+1,6,1)),date(year(j9)+1,6,1),and(date(year(j9)+1,month(j9),day(j9))>

 

[CyrusTheVirus]

Spreadsheet Formulas

Cell Formula Q9 =IF(AND(DATE(YEAR(J9)+1,MONTH(J9),DAY(J9))>=DATE(YEAR(J9)+1,1,1),DATE(YEAR(J9)+1,MONTH(J9),DAY(J9))<DATE(YEAR(J9)+1,6,1)),DATE(YEAR(J9)+1,6,1),AND(DATE(YEAR(J9)+1,MONTH(J9),DAY(J9))>=DATE(YEAR(J9)+1,6,1),DATE(YEAR(J9)+1,MONTH(J9),DAY(J9))<DATE(YEAR(J9)+2,1,1))) <tbody> </tbody>

<tbody>
</tbody>

 

[shg]

This is not pretty ...

	A​	B​	C​
1​	DoH​	Date​	​
2​	01 Jan 2016​	01 Jan 2017​	B2: {=INDEX(DATE(YEAR(A2) + {1,1,2}, {1,7,1}, 1), MATCH(1, DATEDIF(A2, DATE(YEAR(A2) + {1,1,2}, {1,7,1}, 1), "Y"), 0))}
3​	02 Jan 2016​	01 Jul 2017​
4​	01 Jul 2016​	01 Jul 2017​
5​	02 Jul 2016​	01 Jan 2018​
6​	31 Dec 2016​	01 Jan 2018​

 

[CyrusTheVirus]

Hi shg,

That's a wicked formula. I'm going to have to study that one.

Though, I think one could look at it from a point of view that getting hired on Jan 1, 2016 would not make you eligible on Jan 1, 2017, but instead must wait until July 1, 2017 in which case the below formula should work for that. Though, I like your formula a lot. It's very concise.

Spreadsheet Formulas

Cell Formula Q9 =IF(AND<font color="#008000">(DATE(YEAR(J9)+1,MONTH(J9),DAY(J9))>=DATE(YEAR(J9)+1,1,1),DATE(YEAR(J9)+1,MONTH(J9),DAY(J9))<date<font color="#0000FF">(YEAR(J9)+1,7,1)</date), DATE(YEAR(J9)+1,7,1),IF<font color="#008000">(AND<font color="#0000FF">(DATE(YEAR(J9)+1,MONTH(J9),DAY(J9))>=DATE(YEAR(J9)+1,7,1),DATE(YEAR(J9)+1,MONTH(J9),DAY(J9))<date(YEAR(J9)+2,1,1)</date),DATE(YEAR(J9)+2,1,1))) <tbody> </tbody>

<tbody>
</tbody>

 

[starryexcel]

Thank you very much for your replies, no wonder I could not figure out the formula, "wicked" is right!

Note: If they are hired on 1/1 or 7/1 they are eligible exactly one year from those dates. Effectively hired 1/1/15 or 7/1/15, eligible 1/1/16 or 7/1/16. Hired 1/2/15, eligible 7/1/16. Hired 7/2/15, eligible 1/1/17.

 

[starryexcel]

This worked beautifully for me, thank you! Thank you!!!!

I have searched high and low for this formula for over a year!!!

Thanks, Shg...

 

[shg]

You're welcome.

I knew there had to be a more compact way:

	A​	B​	C​
1​	DoH​	DoE​	​
2​	01-Jan-16​	01-Jan-17​	B2: =-HLOOKUP(-EDATE(A2, 12), -DATE(YEAR(A2) + {2,1,1}, {1,7,1}, 1), 1)
3​	02-Jan-16​	01-Jul-17​
4​	01-Jul-16​	01-Jul-17​
5​	02-Jul-16​	01-Jan-18​
6​	31-Dec-16​	01-Jan-18​

 

[shg]

Those pairs of asterisks should be closing curly braces.

 

[shg]

One final simplification:

	A​	B​	C​
1​	DoH​	DoE​	​
2​	01-Jan-16​	01-Jan-17​	B2: =-LOOKUP(-EDATE(A2, 12), -DATE(YEAR(A2) + {2,1,1}, {1,7,1}, 1))
3​	02-Jan-16​	01-Jul-17​
4​	01-Jul-16​	01-Jul-17​
5​	02-Jul-16​	01-Jan-18​
6​	31-Dec-16​	01-Jan-18​