E
Enda80
Excel file for deliberations on proper entry date into a retirement
plan
I work on an Excel file to determine property date of entry. While I
have not completely worked out all of the bugs or the permutations or
problems that may come up, I do feel that I have discerned an
important part of the problem. The sheet where I set up my formulas
and functions has the name 410a elig and coverage and should have a
green tab.
To explain my reasoning; in this hypothetical situation, a company's
plan has two dates of entry, January 1st and July 1st. An employee
enters the plan on which of these two days follows closest or
coincides with his or her attainment of 1,000 hours of service during
the twelve months forming his or her initial eligibly computation
period. If an employee starts work from January 2, 2005 to July 1,
2005, then that employee would enter the plan (provided he or she
worked 1,000 hours during the initial eligibility computation period)
on July 1st, 2006. If that employee started work from July 2, 2005 to
December 31st 2005, then that person would enter the plan on January
1st, 2007.
I used the month function in Excel (which returns single or two digit
number for the month of a date; e.g., January 1, 2008 returns a value
of 1 for January, the first month of the year; July 30, 2006 returns a
value of 7 for July, the seventh month of the year) to determine the
month value for the date of hire of an employee. I then set up an if
value; If that month value equaled 1-6, then the if function returned
a value of 7 (to show entry during the month of July); if the month
value equaled 7 or higher, then that function returned a value of 1
(for entry in the month of July). I may have to revise matters to take
into consideration the case of an employee whose date of hire fell on
January 1, since such an employee would actually enter the plan on the
January 1st of the next year, not the July 1st of the next year, since
January 1st would coincide, not fall after, his or her attainment of
1,000 hours in one year of service.
I have reached the point where the main point I wish to accomplish
entails returning in another cell "January 1, 2007" or "July 1, 2006"
if the results of JJ=1 and Year=2007 in the former case, and "July 1,
2006" if the results of JJ=7 and Year=2006.
Some of the abbreviations or acronyms spelled out:
OYFDOH=One Year From Date of Hire; for now, the issue of leap years I
will set aside
Month Function
Month=Month Function
JJ: January or July
OYFDOH MON JJ Year
8/7/2006 8 1 2007
1/2/2006 1 7 2006
6/1/2006 6 7 2006
3/1/2006 3 7 2006
8/1/2005 8 1 2006
6/1/2006 6 7 2006
plan
I work on an Excel file to determine property date of entry. While I
have not completely worked out all of the bugs or the permutations or
problems that may come up, I do feel that I have discerned an
important part of the problem. The sheet where I set up my formulas
and functions has the name 410a elig and coverage and should have a
green tab.
To explain my reasoning; in this hypothetical situation, a company's
plan has two dates of entry, January 1st and July 1st. An employee
enters the plan on which of these two days follows closest or
coincides with his or her attainment of 1,000 hours of service during
the twelve months forming his or her initial eligibly computation
period. If an employee starts work from January 2, 2005 to July 1,
2005, then that employee would enter the plan (provided he or she
worked 1,000 hours during the initial eligibility computation period)
on July 1st, 2006. If that employee started work from July 2, 2005 to
December 31st 2005, then that person would enter the plan on January
1st, 2007.
I used the month function in Excel (which returns single or two digit
number for the month of a date; e.g., January 1, 2008 returns a value
of 1 for January, the first month of the year; July 30, 2006 returns a
value of 7 for July, the seventh month of the year) to determine the
month value for the date of hire of an employee. I then set up an if
value; If that month value equaled 1-6, then the if function returned
a value of 7 (to show entry during the month of July); if the month
value equaled 7 or higher, then that function returned a value of 1
(for entry in the month of July). I may have to revise matters to take
into consideration the case of an employee whose date of hire fell on
January 1, since such an employee would actually enter the plan on the
January 1st of the next year, not the July 1st of the next year, since
January 1st would coincide, not fall after, his or her attainment of
1,000 hours in one year of service.
I have reached the point where the main point I wish to accomplish
entails returning in another cell "January 1, 2007" or "July 1, 2006"
if the results of JJ=1 and Year=2007 in the former case, and "July 1,
2006" if the results of JJ=7 and Year=2006.
Some of the abbreviations or acronyms spelled out:
OYFDOH=One Year From Date of Hire; for now, the issue of leap years I
will set aside
Month Function
Month=Month Function
JJ: January or July
OYFDOH MON JJ Year
8/7/2006 8 1 2007
1/2/2006 1 7 2006
6/1/2006 6 7 2006
3/1/2006 3 7 2006
8/1/2005 8 1 2006
6/1/2006 6 7 2006