How to return multiple values between two dates in

Question

We have a spreadsheet of all our employees, and I need to generate a list of all employees who's 3 month probation period is about to expire in the coming week.

In other words, I need a list that will automatically display all employees records who's 3 month probation period is about to expire in the next 7 days. It needs to be an array because there will be multiple records each week, and they will change every day.

Column A is their ID number, which is the value I need to return.

Our data looks something like this:

A       B           C
ID      Name        Hire Date
1234    Joe Blow    February 21, 2014
2345    Man Chu     February 26, 2014
3456    Jim Hill    February 26, 2014
4567    Brad Chill  February 28, 2014
5678    Mike Grow   March 5, 2014
6789    Hibs Bee    March 5, 2014
1230    Sarah Mean  March 7, 2014

I've tried Index&Match like this `{=INDEX($A:$A,SMALL(IF(AND($C:$C">="&(today()-90),$C:$C"<="&(today()-80)),ROW($C:$C)),ROW(1:1)),1)} as well as some other formulas, but it's not working, and I can't figure out why.

Any help would be appreciated, thanks very much. This is driving me crazy!

Answer 1

Your comments and formulas indicate you want to find employees whose hire date was between 80 and 90 days ago, inclusive. 90 days will not always be three months but if that is an issue, you can always change the function.

The following array entered formula will return a list of the employee ID's whose hiredate meets those specifications compared with a date in H1. Enter the formula in some cell and fill down as far as might be required. The formula will return blanks when there are no more meeting the specifications. The formula assumes the ID column is column A, and that the first entry is in A2 (header in A1) as you show in your example.

HireDate and ID are named ranges referring, in this case to A2:A8 and C2:C8

=IFERROR(INDEX($A:$A,SMALL(((HireDate+80)<=$H$1)*((HireDate+90)>=$H$1)*ROW(ID),SUM(N(((HireDate+80)<=$H$1)*((HireDate+90)>=$H$1)=0))+ROWS($1:1))),"")

This formula must be array-entered:

---

To array-enter a formula, after entering the formula into the cell or formula bar, hold down ctrl-shift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula.

Answer 2

Define names:

• TargetDate: a cell containing the 90-day target date. You could enter the formula =TODAY() in this cell
• IDCol: the entire column in which you store the employee IDs
• HireDateCol: the entire column in which you store the hire dates

Then in any other column (for example, column D), enter for each record the formula:

=IF(AND(TargetDate-HireDateCol>80,TargetDate-HireDateCol<90),IDCol,"")

What will appear in column D is then either nothing ("") or the ID of the employee whose hire date falls in your desired range. You might want to play with the ">" and "<" to make sure that the range [80,90] is right. Perhaps they should be <= or >=. And you might want to parameterize the 80 and the 90 (enter them into named cells) to make them easy to change if the need ever arises. Also, I don't recommend using the formula =TODAY() in the cell TargetDate. Use an actual date instead. This will allow you to compute the list correctly for any date, past or future; you won't be stuck with today's date. --HTH