Date Range

[Statsman]

Hey guys,

I need your help! I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates.

Example:
1st Table
FROM TO WK
3/27/2009 4/2/2009 14
4/3/2009 4/9/2009 15
4/10/2009 4/16/2009 16
4/17/2009 4/23/2009 17
4/24/2009 4/30/2009 18

2nd Table
DATE
03/28/2009
04/11/2009
04/26/2009

Could you please provide me a simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.

I wanted to code this but there is simple formula, I would go this way.

Your help on this is appreciated!

[Special-K]

Assuming dates are in col A and B and Wk is in column C

=SUMPRODUCT((Table2!A1>=Table1!$A$1:$A$5)*(Table2!A7<=Table1!$B$1:$B$5),Table1!$C$1:$C$5)

[NBVC]

Try:

=LOOKUP(F1,$A$1:$C$5)

where F1 contains date to look up and A1:C5 contains table...

[zbor]

and this?

=INT((G21-39814)/7)+1

where 39814 is for 1.1.2009

[Statsman]

Thanks everyone but the sumproduct works great!

[NBVC]

You did ask for a simple formula...and sumproduct is not the simplist.