View Single Post
  #1   Report Post  
Old December 21st 08, 12:28 PM posted to microsoft.public.excel.links
Enda80 Enda80 is offline
external usenet poster
First recorded activity by ExcelBanter: May 2008
Posts: 7
Default Setting up an Excel sheet to automatically determine when an employeeshould enter a retirement plan, perhaps What-If Analysis would help? Anyother suggestions?

Perhaps a What-If analysis would help with this, but the situation
that presents itself:

For retirement plans, usually a person must work one year of 1,000
hours before that person can enter into the retirement plan as a
participant. However, plans usually have set dates of entry, usually
twice during the year. Therefore, a person enters on one of those
entry dates subsequent to his attainment of 1,000 hours worked during
a one year period. So, for a plan that functions according to a
calendar year (January 1st to December 31st), the dual entry dates
amount to July 1st and January 1st (the day after the end of the six
month of the year and the day after the twelfth month of the year).

So, a person hired on August 1, 2007 who works 1,000 hours would enter
the plan on January 1, 2009 (since August 1, 2008 marks the one year
anniversary of this person's date of hire, and provided that the
person worked enough hours to attain entry into the plan between
August 1, 2007 and August 1, 2008, January 1st would serve as the
first entry date possible).

Some plans get set up sufficiently generous to allow for a slight
retroactive admission (e.g., if a person hired on August 1st, 2007
works 1,000 hours from then to August 1, 2008, that person
retroactively enters the plan on July 1st, 2008). I hope to receive
responses with suggestions on how to set a file in Excel to
automatically gauge both date of entry for plans with normal entry and
slightly retroactive entry. What formulas or macros in Excel can
anyone suggest to accomplish this?

__________________________________________________ __________________________
The person who created this file and asked me to work on it created
this set of guide information.

Date of Hire plus one year nearest date of either July 1 or January 1
For example DOH Aug 1, 2007 Date of Enty will be January 1 2008*
*Though the person who gave me this file included this hypothetical
case in his description, I feel that he may have erred here, since it
seems to me that the person would enter the plan on January 1, 2009.
For example DOH June 1, 2007 Date of Entry will be July 1 2008