1. 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>

2. 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. 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
Exit For
End If
Next intYear
WeekNum = (aDate - datWeek1) 7 + 1
End Function

4. 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. 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

Thank you

6. 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. Re: Week Number (Office 97)

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. 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. Re: Week Number (Office 97)

Hi

Thank you very that works just fine.

I am most grateful for the help

10. Re: Week Number (Office 97)

Hi

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

Thanks again

Posting Permissions

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