offset date

[martysutton]

I am trying to figure out how to write a formula that will tell me what # week it is from a range based on today's date. Example of data below. each set of data is a column. I would like to put the formula at the end of the first column, and base other formula's off this "week". Any help you can provide is appreciated.

Week TY Week Ending
1 200501 200601 2/3/2006
2 200502 200602 2/10/2006
3 200503 200603 2/17/2006
4 200504 200604 2/24/2006
5 200505 200605 3/3/2006
6 200506 200606 3/10/2006
7 200507 200607 3/17/2006
8 200508 200608 3/24/2006
9 200509 200609 3/31/2006
10 200510 200610 4/7/2006
11 200511 200611 4/14/2006
12 200512 200612 4/21/2006
13 200513 200613 4/28/2006
14 200514 200614 5/5/2006
15 200515 200615 5/12/2006
16 200516 200616 5/19/2006
17 200517 200617 5/26/2006
18 200518 200618 6/2/2006
19 200519 200619 6/9/2006
20 200520 200620 6/16/2006
21 200521 200621 6/23/2006
22 200522 200622 6/30/2006
23 200523 200623 7/7/2006
24 200524 200624 7/14/2006
25 200525 200625 7/21/2006
26 200526 200626 7/28/2006
27 200527 200627 8/4/2006
28 200528 200628 8/11/2006
29 200529 200629 8/18/2006
30 200530 200630 8/25/2006
31 200531 200631 9/1/2006
32 200532 200632 9/8/2006
33 200533 200633 9/15/2006
34 200534 200634 9/22/2006
35 200535 200635 9/29/2006
36 200536 200636 10/6/2006
37 200537 200637 10/13/2006
38 200538 200638 10/20/2006
39 200539 200639 10/27/2006
40 200540 200640 11/3/2006
41 200541 200641 11/10/2006
42 200542 200642 11/17/2006
43 200543 200643 11/24/2006
44 200544 200644 12/1/2006
45 200545 200645 12/8/2006
46 200546 200646 12/15/2006
47 200547 200647 12/22/2006
48 200548 200648 12/29/2006
49 200549 200649 1/5/2007
50 200550 200650 1/12/2007
51 200551 200651 1/19/2007
52 200552 200652 1/26/2007
Total
#N/A YTD Ttl

 

[Samnuni]

Vlookup can do what you are seeking and so does Index I believe.

 

[Aladin Akyurek]

Care to specify what are the desired results and what data from the sample they are based on?

 

[martysutton]

my desired result would be to determine the week # (1-52) based on each week ending on friday, and beginning 2-3-06 is wk 1. So hopefully each saturday the # would change to the next weeks corrisponding #'d week. Something like =offset(a4:a55, lookup(today(), d4:d55, a4:a55),0).
However this formula does not seem to give me the result I would like. If i change the dates to read say 1/27/05, then it returns # 2 instead of wk 1. ??

 

[MacGrizz]

You could always use the WEEKNUM function from the Analysis Toolpak. This gives you the week number.

As far as I know, you can only set it up to have the weeks starting on either a Sunday or a Monday. You are looking for each week to end on a Friday (and hence start on a Saturday).

One basic solution would be: =WEEKNUM(A1+1)

This will give you the actual week number for each date, and the week number will increment after each Friday.