Finding the next date in a list

[cthesites]

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!

 

[barry houdini]

If your eligibility date is in C3 and the list of payroll dates is in F2:F10 then you can use this "array formula"

=SMALL(IF(F2:F10>=C3,F2:F10),1)

confirmed with CTRL+SHIFT+ENTER

that finds the smallest date in F2:F10 that is >= C3, i.e. the next one on or after C3. If there are no valid dates you'll get #NUM! error

If you want you can do both calculations in one go with this version getting the payroll date direct from the hire date

=SMALL(IF(F2:F10>=EOMONTH(B3+30,0)+1,F2:F10),1)

 

[cthesites]

I tried this =SMALL(IF(N1:N62>=D3,N1:N62),1) with my sample and it returned 01/04/2013 - the first pay roll date in my list and not the first date after 02/01/2013.

 

[cthesites]

I played around a little and I think I got it to work. Thanks!!!!!