# Inverse to WEEKNUM

Discussion in 'Microsoft Excel Worksheet Functions' started by Alonso, Oct 9, 2008.

1. ### AlonsoGuest

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)

Alonso, Oct 9, 2008

2. ### Sean TimmonsGuest

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.

"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)
>
>

Sean Timmons, Oct 9, 2008

3. ### Don GuillettGuest

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

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

Don Guillett, Oct 10, 2008
4. ### Teethless mamaGuest

=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)
>
>

Teethless mama, Oct 10, 2008
5. ### Don GuillettGuest

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

"Teethless mama" <> wrote in message
news:...
> =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)
>>
>>

Don Guillett, Oct 10, 2008
6. ### cpraoGuest

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)
> >
> >

cprao, Oct 10, 2008
7. ### AlonsoGuest

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)
> >
> >

Alonso, Oct 10, 2008
8. ### AlonsoGuest

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
> news:...
> > 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)
> >> >
> >> >

>
>
>

Alonso, Oct 10, 2008
9. ### AlonsoGuest

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
> news:...
> > 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)
> >> >
> >> >

>
>
>

Alonso, Oct 10, 2008