I am working on an Excel spreadsheet to make sure that everything that has to happen to get a new hire enrolled in benefits happens on time. Our staff are eligible for benefits on the first day of the month after 30 days of employment. (i.e. if the hire date was June 19, 2013 the staff member is eligible on August 1, 2013) I have a column for hire date and a column that calculates the eligibility date. [I used =DATE(YEAR(B3+30),MONTH(B3+30)+1,1)]
Now I need to have Excel go to a list of payroll dates in another column and choose the one that falls on the same date the person becomes eligible or the closest one thereafter. (i.e. If the payroll dates are 08/02/2013, 08/16/2013 and 08/30/2013 and the person's eligibility date is 08/01/2013, I want it to look at the payroll dates and select 08/02/2013).
I have found some things on other forums that talk about returning the first date in a list after today's date and I have tried to modify them, but I seem to be missing something.
I am relatively new to arrays and lookup tables so I would love a very basic, step-by-step answer.
Thank you!
Now I need to have Excel go to a list of payroll dates in another column and choose the one that falls on the same date the person becomes eligible or the closest one thereafter. (i.e. If the payroll dates are 08/02/2013, 08/16/2013 and 08/30/2013 and the person's eligibility date is 08/01/2013, I want it to look at the payroll dates and select 08/02/2013).
I have found some things on other forums that talk about returning the first date in a list after today's date and I have tried to modify them, but I seem to be missing something.
I am relatively new to arrays and lookup tables so I would love a very basic, step-by-step answer.
Thank you!