Eligibility Dates

[Christy]

I am looking for a formula that will give me eligibility dates.

An employee is eligible to participate in 401K the 1st of the month
following 30 days of employment.

Hire Date = 1/2/2008 Date needed 3/1/2008

The same employee is eligible for employer match the 1st of Jan or the 1st
of July following 180 days of employment.

Hire Date = 1/2/2008 Date needed 1/1/2009

 

[Gary''s Student]

The first one is easy:

=DATE(YEAR(A1+30),MONTH(A1+30)+1,1)

 

[Christy]

Ya and i have even harder ones from there.

If an employee has not obtained the age on 21 during the year of hire that
employee is eligible to contribute Jan 1 of the year inwhich he/she obtains
the age of 21 (as long as he/she has fulfilled 30 days of service)

If an employee has not obtained the age on 21 during the year of hire that
employee is eligible for the employer match Jan 1 of the year inwhich he/she
obtains the age of 21 (as long as he/she has fulfilled 180 days of service)

~Example A: Joe Smoe DOB 1/1/1989 Hire Date 8/1/2008

Date eligible for deferral = 1/1/2010
Date eligible for match = 1/1/2010

~Example B: Joe Smoe DOB 1/1/1989 Hire Date 8/1/2009

Date eligible for deferral = 1/1/2010
Date eligible for match = 7/1/2010

 

[Ron Rosenfeld]

I am looking for a formula that will give me eligibility dates.

An employee is eligible to participate in 401K the 1st of the month
following 30 days of employment.

Hire Date = 1/2/2008 Date needed 3/1/2008
=A1+63-DAY(A1+30)-DAY(A1+62-DAY(A1+30))


The same employee is eligible for employer match the 1st of Jan or the 1st
of July following 180 days of employment.

Hire Date = 1/2/2008 Date needed 1/1/2009

I don't understand this Hire Date computation. 2 Jan 2008 + 180 days --> 30
Jun 2008; so wouldn't the date be 1 July 2008 and not 1 Jan 2009?

If the problem is that you forgot that 2008 is a leap year, then try:

=DATE(YEAR(A1+180),7+6*(MONTH(A1+180)>=7),1)

If not, then you'll need to explain more.
--ron