Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2012
    Posts
    56

    Unanswered: Start and end dates from week number

    Hi All

    I have a weekly report that I use a week number to query the data. I want to put the Start and end date on the report. Is there and easy way to extract this from the week number.

    Thanks In Advance

    Bob

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Probably. Which of the various types of week number do you use?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    I would (and have) build a functon where you send the WeekNo, then it returns either the start or enddate (depending on your start day Sun, or Mon)
    This way it fits easy into any query or form.

    Code:
    public Function getWeekNoDate(byval pvWeekNo, byval pvStartOrEnd ) as date
    dim vDate
    
    select case pvStartOrEnd 
      case "S"
          vDate = 'do some calculation for start date
    
      case "E"
          vDate = 'do some calculation for end date
    end select
    
    getWeekNoDate = vDate
    end function

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    Public Function GetStartDate(ByVal WeekNumber As Long) As Date
    
        Dim lngWeek As Long
        Dim lngOffset As Long
        
        lngWeek = DatePart("ww", Date, vbUseSystemDayOfWeek, vbUseSystem)
        lngOffset = DatePart("w", Date, vbUseSystemDayOfWeek, vbUseSystem)
        GetStartDate = DateAdd("ww", WeekNumber - lngWeek, Date)
        GetStartDate = DateAdd("d", -lngOffset, GetStartDate)
        
    End Function
    
    Public Function GetEndDate(ByVal WeekNumber As Long) As Date
    
        GetEndDate = DateAdd("ww", 1, GetStartDate(WeekNumber))
        GetEndDate = DateAdd("d", -1, GetEndDate)
        
    End Function
    Note: Depending on the locale settings and the adopted conventions where you live, you'll perhaps have to change the 2 last parameters of the DatePart() function.

    @ranman256: If all you can provide is:
    Code:
    vDate = 'do some calculation for start date
    and:
    Code:
          vDate = 'do some calculation for end date
    your code is useless!
    Have a nice day!

  5. #5
    Join Date
    Oct 2012
    Posts
    56
    Hi Sinndho

    I have used your code and have it working with one small problem. When I have the week number from two different years which I will have in time ie week 53 I get the same start date for both. Have you any thoughts on this.

    Thanks Bob

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure to understand what you mean. In the immediate window:
    Code:
    ? GetStartDate(53)--> 28/12/2014 
    ? GetEndDate(53)--> 3/01/2015
    Which is correct.

    If you whant to specify a year (which you did not mention in your original post), you could use an optional parameter:
    Code:
    Public Function GetStartDate(ByVal WeekNumber As Long, Optional ByVal SpecificYear As Variant) As Date
    
        Dim lngWeek As Long
        Dim lngOffset As Long
        Dim dte As Date
        
        If IsMissing(SpecificYear) Then
            dte = Date
        Else
            If Len(SpecificYear) = 2 Then SpecificYear = 2000 + SpecificYear
            dte = DateSerial(SpecificYear, Month(Date), Day(Date))
        End If
        lngWeek = DatePart("ww", dte, vbUseSystemDayOfWeek, vbUseSystem)
        lngOffset = DatePart("w", dte, vbUseSystemDayOfWeek, vbUseSystem)
        GetStartDate = DateAdd("ww", WeekNumber - lngWeek, dte)
        GetStartDate = DateAdd("d", -lngOffset, GetStartDate)
        
    End Function
    
    Public Function GetEndDate(ByVal WeekNumber As Long, Optional ByVal SpecificYear As Variant) As Date
    
        GetEndDate = DateAdd("ww", 1, GetStartDate(WeekNumber, SpecificYear))
        GetEndDate = DateAdd("d", -1, GetEndDate)
        
    End Function
    Have a nice day!

  7. #7
    Join Date
    Oct 2012
    Posts
    56

    Solved

    Hello Sinndho

    Many thanks it worked very well. I have spent some time testing and I am getting the results I require. Once again many thanks.

    Cheers Bob

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  9. #9
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Sinndho,
    I dont know his calc for his week. He can do that for his GL.
    I'm just teaching him how to fish.
    No need to be snotty.

Posting Permissions

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