New idea on creating Excel files to automatically determine proper entry

**Enda80** · 01-01-2009, 02:04 PM

As you may recall, I have posted about creating an Excel worksheet which would help determine proper entry into a retirement plan. Another idea that I had entailed setting up a separate worksheet which would return the dates of entry following an employee's date of hire. Therefore, an employee hired on 8/5/2005 would have dates of entry following that stand at 1/1/2006 and 7/1/2006. Then, I had an idea of creating a formula or function to add up the number of hours that the employee had worked.

Setting up a separate worksheet with the number of hours that an employee worked during the initial eligiblity computation period and the subsequent computation periods (separate respective columns for the initial eligibility computation period and then for each subsequent computation period) would help. I would calculate using sum functions.

So, to sketch this out:
One sheet would have the employee's DOH

Another worksheet would have the Dates of Entry subsequent to that DOH (e.g. for an employee hired 8/5/2005 the next Dates of Entry entails 1/1/2006, 7/1/2006, and 1/1/2007)

Yet Another Worksheet would sum the total hours that an employee had worked since DOH on a month by month basis (i.e. the total number of hours than employee had worked since his or her DOH up to a certain point on a monthly basis; e.g. an employee hired on 8/5/2005 would have on the sheet the calculation of the hours this employee had worked as of from 8/5/2005 to 9/1/2005, then the next column would have the total the number of hours worked by the employee from 8/5/2005 to 10/1/2005, etc.)

Finally, the worksheet with the DOH information would have a column which would (this would probably entail heavy use of VLOOKUP) snag the information as to how many months and years an employee had worked as of the subsequent Dates of Entry; if the employee had worked 1,000 hours and 12 months as of 1/1/2007, for example, the employee would enter the plan.

To explain the situation further:
The employee must work 1,000 hours during his or her initial eligibility computation period. That starts on the day of the first hour that an employee works for the company. So, an employee hired on August 1, 2005 who worked one hour on that day must 1,000 hours from then till August 1, 2006 to enter the plan as soon as possible. If the employee did not work 1,000 hours during that period, then the eligibility computation period shifts to the plan year. So, if the employee did not work 1,000 hours between August 1, 2005 and August 1, 2006, but did work 1,000 hours between January 1, 2006 and December 31, 2006 (this of course presumes the plan operates on a calendar year)

**JBeaucaire** · 01-01-2009, 02:50 PM

If dates of working hours are in column A and the total hours for that day's shift are in B, you sum all the hours based on the dates as a filter. You could use something like:

=SUMIF($A$2:$A$10000,">="&"7/1/2005",$B$2:$B$10000)-SUMIF($A$2:$A$10000,">"&"1/1/2007",$B$2:$B$10000) ....to get the second half of 2005. Edit the "dates" to filter for the next six months, the rest of the formula stays the same. Just expand the ranges to include all the data.

You can use that technique to create a monthly summary chart, too, just set the dates to one month ranges.

As for the eligibility, I'd check for each item the same way. DOH is in cell D1, then the same formula can be used to see if 1000 hours is reached by a specific date or not:

=SUMIF($A$2:$A$10000,">="&D1,$B$2:$B$10000)-SUMIF($A$2:$A$10000,">"&D1+365,$B$2:$B$10000)

Do an "IF" against that formula for the 1000, and you know if they made it or not:

=if(SUMIF($A$2:$A$10000,">="&D1,$B$2:$B$10000)-SUMIF($A$2:$A$10000,">"&D1+365,$B$2:$B$10000)>=1000, "eligible", "ineligible")

**Enda80** · 01-02-2009, 11:50 AM

Thank you for your help, it proved quite enlightening and instructive.

Note that I inserted two worksheets, Hours Worked By Month and Subsequent DOE. The latter sheet should produce the dates of entry following an employee's date of hire (e.g., an employee gets hired on 8/2/05, then subsequent dates of entry include 1/1/2006, 7/1/2006, 1/1/2007, and 7/1/2007). I have as yet not found a way to automate that process. I will do some further postings on message boards.

Also, another problem:
you will notice that in the sheet named Hours Worked By Month, I started with 1/1/2003 and then added 31 days to try to get to the first days of subsequent months. While approximating what I hoped to achieve (i.e. reaching the first day of subsequent months) one can see that I do not actually arrive at this desired result, since the 28-29 days of February, months less than 31 days long, and leap years got in the way.

**JBeaucaire** · 01-02-2009, 01:09 PM

I don't see any work at all on Hours Worked By Month, so I don't know what layout you're trying to achieve.

**JBeaucaire** · 01-02-2009, 01:23 PM

Sorry, it was a little higher, my bad.

In C1, enter:

=DATE(YEAR(B1),MONTH(B1)+1,DAY(B1))

...and copy to the right.

**Enda80** · 01-02-2009, 04:49 PM

Well, thank you for your help and attention, I have revised the files appropriately.

That said, the earlier problem still remains:

Note that I inserted two worksheets, Hours Worked By Month and Subsequent DOE. The latter sheet should produce the dates of entry following an employee's date of hire (e.g., an employee gets hired on 8/2/05, then subsequent dates of entry include 1/1/2006, 7/1/2006, 1/1/2007, and 7/1/2007). I have as yet not found a way to automate that process. I will do some further postings on message boards.

**JBeaucaire** · 01-02-2009, 07:28 PM

A lookup table can fix that for you, look at this addition on Subsequent DOE.

**JBeaucaire** · 01-02-2009, 09:06 PM

Added error checking to the new formulas on SUBSEQUENT DOE.

**Enda80** · 01-02-2009, 11:44 PM

Well, quite impressive, thank you very much. I have passed this on to my associate. Hopefully, this will serve matters well in our work. Thank you for your help and attention.