Excel file for deliberations on proper entry date into a retirementplan
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 |
Excel file for deliberations on proper entry date into a retiremen
I think that adding a column similar to this will help:
="1st "& IF(C15=1,"January "," July ") & D15 where column C is your column JJ and column D is your column Year HTH "Enda80" wrote: 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 |
All times are GMT +1. The time now is 10:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com