Results 1 to 3 of 3
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Week number dates (95/97/2000)

    I want to enter a week number in one cell, then use this week number to have the appropriate weekstarting date calculate in an adjoining cell. I seem to recall there is a function but cannot for the life of me find this in the Help file. Or am I dreaming and need to create a lookup table?
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Week number dates (95/97/2000)

    If your defined week starts January 1 (which was a Tuesday in 2002), if the week numbers start in A1 and run down, in B1 enter:

    =DATE(YEAR(TODAY()),1,0)+A1*7-6

    format as date, copy down. (Substitute YEAR(TODAY()) with something else appropriate if you're looking at other years than the current one.)

    If your weeks are numbered starting on the first Sunday in the year, I think this will work:

    =DATE(YEAR(TODAY()),1,0)+A1*7+1-MOD(DATE(YEAR(TODAY()),1,0),7)

    If you use another definition of start week, you should post it.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. The Following User Says Thank You to JohnBF For This Useful Post:

    FiL (2016-11-27)

  4. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Week number dates (95/97/2000)

    Hey good one, many thanks
    <img src=/S/clever.gif border=0 alt=clever width=15 height=15><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •