Inverse to WEEKNUM

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

  1. Alonso

    Alonso Guest

    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
    #1
    1. Advertisements

  2. Alonso

    Sean Timmons Guest

    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
    #2
    1. Advertisements

  3. Alonso

    Don Guillett Guest

    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
    #3
  4. =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
    #4
  5. Alonso

    Don Guillett Guest

    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
    #5
  6. Alonso

    cprao Guest

    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
    #6
  7. Alonso

    Alonso Guest

    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
    #7
  8. Alonso

    Alonso Guest

    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
    #8
  9. Alonso

    Alonso Guest

    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
    #9
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sue Pixton

    WEEKNUM for financial year

    Sue Pixton, Jul 17, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    730
    Sue Pixton
    Jul 18, 2003
  2. wank

    weeknum function

    wank, Jul 22, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    377
    Norman Harker
    Jul 22, 2003
  3. Roger

    WeekNum function in vba??

    Roger, Oct 14, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    2,006
    Andy Wiggins
    Oct 14, 2003
  4. Guest

    Weeknum and an Array

    Guest, Nov 18, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    863
    Harlan Grove
    Nov 18, 2003
  5. Bcel

    Excel WEEKNUM (Date & Time) Question

    Bcel, Jan 22, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    7
    Views:
    462
    Norman Harker
    Jan 22, 2004
Loading...

Share This Page