PC Review


Reply
Thread Tools Rate Thread

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)


 
Reply With Quote
 
 
 
 
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)
>
>

 
Reply With Quote
 
 
 
 
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
(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)
>
>


 
Reply With Quote
 
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)
>
>

 
Reply With Quote
 
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)
>>
>>


 
Reply With Quote
 
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)
> >
> >

 
Reply With Quote
 
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)
> >
> >

 
Reply With Quote
 
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)
> >> >
> >> >

>
>
>

 
Reply With Quote
 
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)
> >> >
> >> >

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
WEEKNUM Question RustyR Microsoft Excel Programming 5 4th Jan 2005 04:13 PM
the formula for weeknum in Office 2003 doesn;t work...=weeknum(A2. SidBord Microsoft Excel Crashes 1 20th Sep 2004 07:39 AM
Weeknum-function =?Utf-8?B?VG9iaWFz?= Microsoft Excel Programming 1 4th Jun 2004 01:33 PM
WEEKNUM function returns #name in Excel 2003 Alan K. Martinez Microsoft Excel Discussion 3 25th Jan 2004 09:46 AM
Weeknum Add In doesn't load when launching Excel Eugene Microsoft Excel Programming 1 15th Dec 2003 03:05 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:17 PM.