Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 89
Default Eligibility Dates

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Eligibility Dates

The first one is easy:

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


--
Gary''s Student - gsnu200793


"Christy" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 89
Default Eligibility Dates

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




"Gary''s Student" wrote:

The first one is easy:

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


--
Gary''s Student - gsnu200793


"Christy" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Eligibility Dates

On Fri, 20 Jun 2008 11:56:00 -0700, Christy
wrote:

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I sort a column of random dates into Consecutive dates Rob Gibson Excel Worksheet Functions 2 June 12th 07 05:10 AM
Identifying unique dates within a range of cells containing dates cdavidson Excel Discussion (Misc queries) 0 October 12th 06 08:19 PM
need to convert list of dates to count no. of dates by week neowok Excel Worksheet Functions 13 January 30th 06 04:54 PM
Calculating number of days between two dates that fall between two other dates [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM
Charting data against dates where dates are not at fixed intervals PK Charts and Charting in Excel 4 June 16th 05 05:08 AM


All times are GMT +1. The time now is 10:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"