Week to date

[Tom Ogilvy]

use match to find the weeknumber, then the result as an argument to Index to
get the value(s) you want

=Index(DATERANGE,Match(27,WeekRange,0),1)

where 27 is the weeknum you are looking for.

--
Regards,
Tom Ogilvy

I have a spreadsheet the holds the Date, week #, Day,,Month,Year. in
separate columns for this year and the next 10. This I've been using to
find a date and give me the corresponding week number. Now I want to turn
it around slightly and search for a week number and have both start and end
date of the week returned.

The spreadsheet is laid out as:

DateWeekDayMonthYear
01/01/20001112000
02/01/20001212000
03/01/20001312000
04/01/20001412000
05/01/20001512000
06/01/20001612000
07/01/20001712000
08/01/20002812000
09/01/20002912000
10/01/200021012000

etc.
etc.

I use a Vlookup(date,whole range of info,2,FALSE) to return the week number
but as the week number is the second column in the data range I can't do it
in reverse, Vlookup(Week number,whole range of info,-1,FALSE)!!
How can I find the first date for week 1 and the last date.

Thanks

Dean
(e-mail address removed)
http://www.dkso.co.uk/
http://homepage.ntlworld.com/dkso

 

[Frank Kabel]

Hi
for the first date:
=INDEX(A1:A100,MATCH(weeknumber,B1:B100,0))

For the last one. If your data is sorted this way try:
=INDEX(A1:A100,MATCH(weeknumber+1,B1:B100,0)-1)

--
Regards
Frank Kabel
Frankfurt, Germany

I have a spreadsheet the holds the Date, week #, Day,,Month,Year. in
separate columns for this year and the next 10. This I've been using
to find a date and give me the corresponding week number. Now I want
to turn it around slightly and search for a week number and have both
start and end date of the week returned.

The spreadsheet is laid out as:

Date Week Day Month Year
01/01/2000 1 1 1 2000
02/01/2000 1 2 1 2000
03/01/2000 1 3 1 2000
04/01/2000 1 4 1 2000
05/01/2000 1 5 1 2000
06/01/2000 1 6 1 2000
07/01/2000 1 7 1 2000
08/01/2000 2 8 1 2000
09/01/2000 2 9 1 2000
10/01/2000 2 10 1 2000

etc.
etc.

I use a Vlookup(date,whole range of info,2,FALSE) to return the week
number but as the week number is the second column in the data range I
can't do it in reverse, Vlookup(Week number,whole range of
info,-1,FALSE)!!
How can I find the first date for week 1 and the last date.

Thanks

Dean
(e-mail address removed)
http://www.dkso.co.uk/

http://homepage.ntlworld.com/dkso

 

[Dean]

I have a spreadsheet the holds the Date, week #, Day,,Month,Year. in separate columns for this year and the next 10. This I've been using to find a date and give me the corresponding week number. Now I want to turn it around slightly and search for a week number and have both start and end date of the week returned.

The spreadsheet is laid out as:

Date Week Day Month Year
01/01/2000 1 1 1 2000
02/01/2000 1 2 1 2000
03/01/2000 1 3 1 2000
04/01/2000 1 4 1 2000
05/01/2000 1 5 1 2000
06/01/2000 1 6 1 2000
07/01/2000 1 7 1 2000
08/01/2000 2 8 1 2000
09/01/2000 2 9 1 2000
10/01/2000 2 10 1 2000

etc.
etc.

I use a Vlookup(date,whole range of info,2,FALSE) to return the week number but as the week number is the second column in the data range I can't do it in reverse, Vlookup(Week number,whole range of info,-1,FALSE)!!
How can I find the first date for week 1 and the last date.

Thanks

Dean
(e-mail address removed)
http://www.dkso.co.uk/

http://homepage.ntlworld.com/dkso