Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Week Number (Office 97)

    Hi

    A kind lounger gave me the following to get the date from a week Number.
    =DATE($Q$1,1,1)+(C3-IF(WEEKDAY(DATE($Q$1,1,1),2)<5,1,0))*7-WEEKDAY(DATE($Q$1,1,1),2)+1
    You've gussed it, now I would like to enter a date and get a week Number.

    <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Week Number (Office 97)

    Use the WEEKNUM function. It is available in the analysis toolpack. See HELP (under WEEKNUM) for adding it, if it is not installed and the parameters used.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Week Number (Office 97)

    The Analysis Toolpak add-in has a WEEKNUMBER function. However, this function uses the US numbering system (week 1 always starts on the 1st of January). If that suits you, look no further.

    Many European countries use the ISO 8601 system: the first calendar week of the year is the one that includes the first Thursday of that year, or, equivalently, the week that contains the 4th of January of that year.

    The VB function Format(SomeDate, "ww", vbUseSystemDayOfWeek, vbUseSystem) uses the numbering system corresponding to Regional Settings, but it may return incorrect results - see BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year. This MSKB article has a workaround.

    The following function computes the correct week number for a given date using ISO 8601. It's a lot faster than Format too.

    Function WeekNum(aDate As Date) As Integer
    Dim datJan4 As Date
    Dim datWeek1 As Date
    Dim intYear As Integer
    Dim intDayOfWeek As Integer
    For intYear = Year(aDate) + 1 To Year(aDate) - 1 Step -1
    datJan4 = DateSerial(intYear, 1, 4)
    intDayOfWeek = WeekDay(datJan4, vbUseSystemDayOfWeek)
    datWeek1 = datJan4 + 1 - intDayOfWeek
    If aDate >= datWeek1 Then
    Exit For
    End If
    Next intYear
    WeekNum = (aDate - datWeek1) 7 + 1
    End Function

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Week Number (Office 97)

    WEEKNUMBER must be from later editions of Excel.

    In XL97 the function is WEEKNUM and you use the optional parameter of 1 (default) for weeks starting on Sun) or 2 to start the week on Monday.

    WEEKNUM (in XL97) does NOT start the week with Jan 1. It begins on the SUNDAY (or MONDAY) that Jan 1st is in.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Week Number (Office 97)

    Hi

    Hans thank you for your prompt reply, but being a novice I am not sure where to put the scipt and how to use it.

    I did try but got a result of zero

    Can you enlighten me please

    Thank you
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Week Number (Office 97)

    Steve,

    Sorry about the confusion; I actually meant the WEEKNUM function. I should have checked this better - I use the Dutch-language version of Excel 97 and the macro recorder didn't translate this worksheet function back into English (it does translate standard Excel functions back into English).

    Anyway, in my Dutch Excel 97 SR2, the WEEKNUM function always returns 53 (or 54) for December 31, and 1 for January 1. So week 1 doesn't start on a Sunday (or Monday), but on January 1. Week 2 starts on the first Sunday (or Monday) after January 1. In this system, week 1 (and week 53) usually have less than 7 days.

    In the ISO 8601 system used in my country, each week number contains 7 days, but January 1 is not necessarily in week 1 - it may be in week 52 or 53 of the preceding year; this happened for instance in the year 2000.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Week Number (Office 97)

    Braddy,

    You can put the code in Personal.xls. If you don't know what that is, first take a look at Legare Coleman's excellent <!post=Personal.xls Tutorial, 118382>Personal.xls Tutorial<!/post> before you continue.

    Once you have created Personal.xls, you can copy the code and paste it into a standard module. You can use it in your worksheets in the form

    =Personal.xls!WeekNum(A1)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Week Number (Office 97)

    WEEKNUM will always give Jan 1 as 1 since it is ALWAYS in the first week of the year. It is NOT always the FIRST DAY of Week 1. The first day(s) of week 1 might NOT "exist" (they actually default to the week of the YEAR they are IN).

    If Jan 1 is on a Wed then Week 1 starts on Sun 12/29 (or Mon 12/30) though the days in December are NOT classified as part of WEEK 1 in This year, they are classed as the LAST week of the Previous year.

    Dec 31 is ALWAYS in the LAST week of the year so it will give 52 (or 53).

    I guess it depends on WHEN you define a YEAR starting. In WEEKNUM the year STARTS on Jan 1 and ends on Dec 31. Week 53 of LAST YEAR will OVERLAP with Week 1 of THIS year. The weeknumber given is based on the YEAR of the date.

    I understand that Iso8601 does NOT work this way. Hence the need for your own routines.

    Steve

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Week Number (Office 97)

    Hi

    Thank you very that works just fine.

    I am most grateful for the help
    If you are a fool at forty, you will always be a fool

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Week Number (Office 97)

    Hi

    Thank you for your reply, I have used Hans's code and it works fine

    Thanks again
    If you are a fool at forty, you will always be a fool

Posting Permissions

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