Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 date entry: Cannot get away from d-m-yy entry format | Excel Discussion (Misc queries) | |||
what is the proper file format for excel 2003 | Excel Programming | |||
Automate Excel download of csv file without proper URL? | Excel Programming | |||
Excel add-in w/ Library.dll file, ? proper folder for the dll | Excel Discussion (Misc queries) | |||
Auto Proper Case on Entry | Excel Worksheet Functions |