How to compare a date against a list of date and.... 1

[nvtjellis]

I have a list of dates work was done. The dates (A) fall within a pay period (B). How do I compare the date to the list of dates in a column, determine if the input value is within the pay period & if true, insert the pay day (C) for which that work day will be pay?
There is 14 days within the pay period. Payday for that pay period is 7 day later, or 21 days after from the beginning of the pay period.
My problem is comparing down column B1 and finding the ending pay period that the Workday falls within.

Here is attempt at the solution:
=IF(AND(A1<B1:B11,B1-A1<=14),C1)

The first row will work, but 2-3. I don’t know how to increment down, otherwise loop thru…
A B C
Work Day Ending Pay Period Payday
1 12/1/2006 12/15/06 12/22/06
2 12/2/2006 12/29/06 01/05/07
3 12/3/2006 01/12/07 01/19/07
4 12/07/06 01/26/07 02/02/07
5 12/08/06 02/09/07 02/16/07
6 02/23/07 03/02/07
7 03/09/07 03/16/07
8 03/23/07 03/30/07
9 04/06/07 04/13/07
10 04/20/07 04/27/07
11 05/04/07 05/11/07

 

[Gavona]

Create a table, 2 columns

Date of first day in pay period, Pay day

Name the range "PayDayLookUp"

=vlookup([cell containing date to lookup],PatDayLookUp,2,1)

(Have a look at help to see why this works)

If you don't use a named range then make sure that the range reference is absolute, eg
=VLOOKUP(G6,$C$6:$D$8,2)


Gavin

 

[nvtjellis]

Gavin, the "dates work" is a date within a 14 day period.
The "dates work" could be any date prior to the
"Ending Pay Period" date. I inturprate your responds to indecate the "dates work" would be in the table, Am I correct? If so, the odds of it being in the table is small. Unless you are suggesting the table to include all days within the ending pay period, in which case the table would be large. What I would like to do is determine if the "dates work" is within the Ending Pay Period. The proof of "dates work" being in the Ending Pay Period is the differance between the "Ending Pay Period" and the "dates work", in this case 0-14.

 

[Gavona]

The table only needs to contain the first date in the period and the pay date and must be sorted. Try it and see!

With the final parameter of vlookup set to True (or 1) then the function will find the closest match equal to or lower than the date you look up.




Gavin