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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Sue Pixton

    WEEKNUM for financial year

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

    weeknum function

    wank, Jul 22, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    603
    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,550
    Andy Wiggins
    Oct 14, 2003
  4. Guest

    Weeknum and an Array

    Guest, Nov 18, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    2,134
    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:
    619
    Norman Harker
    Jan 22, 2004
  6. Dave

    Excel WeekNum value doesn't match PDAs for 2005

    Dave, Feb 5, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    277
    Guest
    Feb 5, 2004
  7. david p

    WEEKNUM function

    david p, Mar 11, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    8
    Views:
    407
    david p
    Mar 13, 2004
  8. Jmbostock

    Convert WEEKNUM into last day Date

    Jmbostock, Mar 16, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    322
    Norman Harker
    Mar 17, 2004
Loading...