Inverse to WEEKNUM

A

Alonso

I know that using the function WEEKNUM you can get the number for a week
through a year

eg. =WEEKNUM(09/29/2008) gets 40

my question is
can it be done backwards??

what i want to do is
type the number of the week (A1)
and get the date of the monday of that week (on B1)

ie
if i type 41
i should get 10/06/2008
(the date of the monday of week 41)
 
S

Sean Timmons

Maybe not the prettiest thing, but if you put 1/1/08 in B1 and 40 in A1,

=IF(WEEKDAY(B1+A1*7,1)=2,WEEKDAY(B1+A1*7,1),WEEKDAY(B1+A1*7,1)-WEEKDAY(B1+A1*7,1)+2)

should get you there.
 
D

David Biddulph

=DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1))+7*(A2-1)+2
but note that in some years, such as this, there isn't a Monday in week 1
(as it's actually week 53 of the previous year).
 
D

David Biddulph

The 8 is OK for 2008, but not for other years.
See other formulae suggested.
 
D

David Biddulph

.... providing that YEAR(TODAY()) is 2008, so in that case you could use
=DATE(2008,1,1)-8+(A1)*7

For other years, you need one of the other formulae suggested (or alter
the -8 parameter).
 
A

Alonso

thanks for the remark David

i noticed that your formula is similar
I'll keep both, just to track any changes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top