Visual Basic Forum for Visual Basic Programmers VB

[Enda80]

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