inverse of weeknum

[shaise]

how do i do the opposite of the weeknum function? that is, i have a cell with a week number in it, and want to be able to get a date for that week.
thanks

[zzydhf]

(weeknum * 7) + 1/1/02

[Yogi Anand]

Hi zzyddhf:

Just an editorial correction ...

=weeknum*7+"1/1/02"

Regards!

NB: You may also have to adjust it down by 1 week ... depending upon what you are looking for

[ This Message was edited by: Yogi Anand on 2002-05-24 11:53 ]

[IML]

assuming year of interest in B1 and weeknumber in A1, you could use
=DATE(B1,1,A1*7-(DAY(DATE(B1,1,1)+7-WEEKDAY(DATE(B1,1,1),2)))-1)

to provide the monday of the week. good luck

Edit
Since Weeknum(a1) defaults to the Sunday start date, Sunday may be more approriate, simply tweek to
=DATE(B1,1,A1*7-(DAY(DATE(B1,1,1)+7-WEEKDAY(DATE(B1,1,1),2)))-2)

[ This Message was edited by: IML on 2002-05-24 11:57 ]

[shaise]

thanks. works when putting 1/1/02 in quotation marks. otherwise, returns date with year 2000.

[IML]

On 2002-05-24 11:52, shaise wrote:
thanks. works when putting 1/1/02 in quotation marks. otherwise, returns date with year 2000.

If this formula works for you great, leave it alone. Just be aware this doesn't necessary follow the same calc as the weeknum function. For example, if you plug in week 1 for any year, your value will be 01/08/xx, which is really the second week.