Date Range Formula Question - HELP!

[Noclueatall]

Hello, I'm having trouble with a formula and I'm hoping someone can
help. Sample Data Includes the following:

Pay Period Start Pay Period End Pay Period #
12/16/01 12/29/01 26
12/30/01 01/12/02 25
01/13/01 01/26/02 24
01/27/02 02/09/02 23
02/10/02 02/23/02 22

The pay periods continue until there are 26 pay periods for the entire
year.

Sample date: 01/30/02

Taking into account there are 26 pay periods in my sheet, I need a
formula that will return the result of the pay period # if the sample
date falls within the range of the pay period. Thanks a ton in
advance!

 

[Andy B]

Noclue

Try this:
=VLOOKUP(sample date,table of data,3,TRUE)

Andy

 

[Bob Phillips]

Assuming the sample date is in E1, start dates in A, end in BE, periods in C
try

=SUMPRODUCT((A1:A100<=E1)*(B1:B100>=E1),(C1:C100))

will fail if there is more than one row covering the sample date, as it will
add the periods.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Noclueatall]

Thanks to both of you!