401k Eligibility Dates

Question

I have seen similar posts regarding 401k eligibility dates, but they were not quite what I was looking for.

I have a list of employees' start dates. They become eligible to enroll in the plan on the first day of the month following 180 days of service. But, they can only enroll in the plan on May 1 and November 1.

Example, an employee hired January 20, 2010 would be eligible August 1, 2010 but could not enroll in the plan until November 1, 2010.

How can I create a formula that rounds their eligibility date (date of hire plus 180 days of service) to the nearest upcoming six month period (May 1 or November 1)?

Here is what I have so far, but it has some issues...

=DATE(YEAR(D2),11+6*(MONTH(D2+6)>6)-6*(DAY(D2)=1),1)

Hire date is in column D.

Any help would be appreciated.

Andrew

Answer 1

With your Hire Date in A1, try this formula for the next enrollment date:

=MIN(DATE(YEAR(A1+180)+(DATE(YEAR(A1+180),11,1)<(A1+180)),11,1),

DATE(YEAR(A1+180)+(DATE(YEAR(A1+180),5,1)<(A1+180)),5,1))

But what should happen if someone is hired on 5 May 2014.  180 days later is 1 November 2014.  Can they enroll on that date, or do they have to wait until the next May.  The formula above assumes they won't have to wait.

Answer 2

That is correct. If they are eligible on November 1, 2014, they can enroll on November 1, 2014.

The formula works great. Thanks!