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

 

[starryexcel]

Shg, you are truly amazing, absolutely amazing! Thank you so much.

I had no idea how exciting asking a question on here would be...

 

[shg]

You're welcome.

 

[CyrusTheVirus]

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​

<tbody>
</tbody>

shg,

Would you mind explaining how this works?

I understand the DATE/YEAR breakout and how the array constants affect that part of the calculation. But I'm not understanding why you have put negation signs throughout the formula and why you chose to put dates from latest to earliest?