# Inverse to WEEKNUM

Alonso
Guest
Posts: n/a

 9th Oct 2008
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
Guest
Posts: n/a

 9th Oct 2008
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)
>
>

Don Guillett
Guest
Posts: n/a

 10th Oct 2008
try this
=DATEVALUE("Dec 31, 2007")+(\$B\$2-1)*7
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
http://www.pcreview.co.uk/forums/(E-Mail Removed)
"Alonso" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
Guest
Posts: n/a

 10th Oct 2008
=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
Guest
Posts: n/a

 10th Oct 2008

Looks like the best one
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Teethless mama" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =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
Guest
Posts: n/a

 10th Oct 2008
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)
> >
> >

Alonso
Guest
Posts: n/a

 10th Oct 2008
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
Guest
Posts: n/a

 10th Oct 2008
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
Guest
Posts: n/a

 10th Oct 2008
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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)
> >> >
> >> >

>
>
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post RustyR Microsoft Excel Programming 5 4th Jan 2005 04:13 PM SidBord Microsoft Excel Crashes 1 20th Sep 2004 07:39 AM =?Utf-8?B?VG9iaWFz?= Microsoft Excel Programming 1 4th Jun 2004 01:33 PM Alan K. Martinez Microsoft Excel Discussion 3 25th Jan 2004 09:46 AM Eugene Microsoft Excel Programming 1 15th Dec 2003 03:05 PM

Features