Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Week Number of Date

  1. #1
    Join Date
    28th February 2005
    Posts
    72

    Week Number of Date

    Hello again,

    I'm using excel 2003 and I searching for a small code to automaticly generate the begin- and end-date of a week (from monday till sunday)
    the only variable that I wanna give is the Weeknumber.

    So if I write a weeknumber in cell(a,1)
    I want the begin-date (monday) in cell (b,1) and
    I want the end-date (sunday) in cell (c,1).

    for example:
    A B C
    1 36
    2 04-09-2006
    3 10-09-2006
    4

    can anybody help me with this??

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,566

    Re: dates by weeknumber

    Before proceeding, I think you should reach this article on weeknumbers in excel. It's pretty comprehensive.

    Then if you could decide which type of week numbering you are using we can probably come up with a solution.
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,566

    Re: dates by weeknumber

    Personally I use the functions written by John Green

    VB:
     'Functions written by John Green
    Public Function YearStart(WhichYear As Integer) As Date 
         
        Dim WeekDay As Integer 
        Dim NewYear As Date 
         
        NewYear = DateSerial(WhichYear, 1, 1) 
        WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0
         
        If WeekDay < 4 Then 
            YearStart = NewYear - WeekDay 
        Else 
            YearStart = NewYear - WeekDay + 7 
        End If 
         
    End Function 
     
     
    Public Function WeekStart(WhichWeek As Integer, WhichYear As _ 
        Integer) As Date 
         
        WeekStart = YearStart(WhichYear) + ((WhichWeek - 1) * 7) 
         
    End Function 
    
    
    Assume you have 2006 in B2
    You have weeknumbers 1 to 53 in A4 down

    in B4 =weekstart(A4,$B$1)

    returns 02 Jan 2006
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: dates by weeknumber

    Hi,

    Something this will works for current year(2006)

    In A2,

    =DATE(2006,1,1)-WEEKDAY(DATE(2006,1,1))+A1*7-5

    In A3,

    =A2+6

    HTH

  5. #5
    Join Date
    28th February 2005
    Posts
    72

    Re: dates by weeknumber

    thanx Krishnakumar...

    that does the trick that I wanted..

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Week Number For Date Relative To Date Range
    By Absa in forum Excel General
    Replies: 4
    Last Post: March 18th, 2008, 15:45
  2. Get Week Number From Date
    By chewingyu in forum Excel General
    Replies: 8
    Last Post: February 21st, 2008, 12:07
  3. Date To Week Number & Year
    By nyki in forum Excel General
    Replies: 5
    Last Post: October 9th, 2007, 02:13
  4. Week Number From Date
    By Andie in forum Excel General
    Replies: 14
    Last Post: November 22nd, 2006, 06:25
  5. Convert a date to a week number
    By Gethzerion in forum Excel General
    Replies: 5
    Last Post: June 22nd, 2005, 19:26

Bookmarks

Posting Permissions

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