Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Week Number of Date

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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??

  • #2
    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

    Comment


    • #3
      Re: dates by weeknumber

      Personally I use the functions written by John Green


      '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

      Comment


      • #4
        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
        Kris

        ExcelFox

        Comment


        • #5
          Re: dates by weeknumber

          thanx Krishnakumar...

          that does the trick that I wanted..

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X