Inverse to WEEKNUM
 

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)

Inverse to WEEKNUM
 

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),WEEKDA Y(B1+A1*7,1)-WEEKDAY(B1+A1*7,1)+2)

should get you there.

"Alonso" wrote:

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)

Inverse to WEEKNUM
 

try this
=DATEVALUE("Dec 31, 2007")+($B$2-1)*7
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Alonso" wrote in message
...
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)

Inverse to WEEKNUM
 

=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).
--
David Biddulph

"Alonso" wrote in message
...
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)

Inverse to WEEKNUM
 

=DATE(YEAR(TODAY()),1,1)-8+(A1)*7


"Alonso" wrote:

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)

Inverse to WEEKNUM
 

Looks like the best one
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Teethless mama" wrote in message
...
=DATE(YEAR(TODAY()),1,1)-8+(A1)*7


"Alonso" wrote:

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)

Inverse to WEEKNUM
 

Hi Mama
I am curious to know why you deducted 8 in the formula
--
cprao


"Teethless mama" wrote:

=DATE(YEAR(TODAY()),1,1)-8+(A1)*7


"Alonso" wrote:

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)

Inverse to WEEKNUM
 

The 8 is OK for 2008, but not for other years.
See other formulae suggested.
--
David Biddulph

"cprao" wrote in message
...
Hi Mama
I am curious to know why you deducted 8 in the formula
--
cprao


"Teethless mama" wrote:

=DATE(YEAR(TODAY()),1,1)-8+(A1)*7


"Alonso" wrote:

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)

Inverse to WEEKNUM
 

as Don says
looks like the best one

and above all
it worked perfectly

thanks!!


"Teethless mama" wrote:

=DATE(YEAR(TODAY()),1,1)-8+(A1)*7


"Alonso" wrote:

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)

Inverse to WEEKNUM
 

.... 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).
--
David Biddulph

"Alonso" wrote in message
...
as Don says
looks like the best one

and above all
it worked perfectly

thanks!!


"Teethless mama" wrote:

=DATE(YEAR(TODAY()),1,1)-8+(A1)*7


"Alonso" wrote:

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)

Inverse to WEEKNUM
 

thanks for the remark David

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



"David Biddulph" wrote:

.... 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).
--
David Biddulph

"Alonso" wrote in message
...
as Don says
looks like the best one

and above all
it worked perfectly

thanks!!


"Teethless mama" wrote:

=DATE(YEAR(TODAY()),1,1)-8+(A1)*7


"Alonso" wrote:

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)

Inverse to WEEKNUM
 

David

for year 2009
what changes should be made to the formulas
either your's or mama's


"David Biddulph" wrote:

.... 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).
--
David Biddulph

"Alonso" wrote in message
...
as Don says
looks like the best one

and above all
it worked perfectly

thanks!!


"Teethless mama" wrote:

=DATE(YEAR(TODAY()),1,1)-8+(A1)*7


"Alonso" wrote:

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)

Inverse to WEEKNUM
 

My original reply

will work for 2009, as for other years.
--
David Biddulph

"Alonso" wrote in message
...
David

for year 2009
what changes should be made to the formulas
either your's or mama's


"David Biddulph" wrote:

.... 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).
--
David Biddulph

"Alonso" wrote in message
...
as Don says
looks like the best one

and above all
it worked perfectly

thanks!!


"Teethless mama" wrote:

=DATE(YEAR(TODAY()),1,1)-8+(A1)*7


"Alonso" wrote:

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)