Week Numbers of the Year

[curtterp]

Just uploaded to the Gallery "WeekByDate"

it has 30 years of dates and week of the year

Are your week numbers ISO or US American?

Whatever Microsoft uses in their Excel built in formula..... I have no idea which it is

[uboche]

The WEEKNUM function in EXCEL 2010 and later das two arguments:

WEEKNUM(serial_number,[return_type])

If you specify WEEKNUM(serial_number,1) or just WEEKNUM(serial_number), you get US American week numbers.
If you specify WEEKNUM(serial_number,21), you get ISO week numbers which are used across Europe.

System 1 The week containing January 1 is the first week of the year, and is numbered week 1.
System 2 The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.

In addition: In the US, weeks start on Sunday. In Europe, weeks start on Monday.

[curtterp]

The WEEKNUM function in EXCEL 2010 and later das two arguments:

WEEKNUM(serial_number,[return_type])

If you specify WEEKNUM(serial_number,1) or just WEEKNUM(serial_number), you get US American week numbers.
If you specify WEEKNUM(serial_number,21), you get ISO week numbers which are used across Europe.

System 1 The week containing January 1 is the first week of the year, and is numbered week 1.
System 2 The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.

In addition: In the US, weeks start on Sunday. In Europe, weeks start on Monday.

then it is American week numbers

I didnt know there was a difference. Thank you for the explanation

[royaltiger]

Formula Description Result

=WEEKNUM(A2) Number of the week in the year that 3/9/2012 occurs,
based on weeks beginning on Sunday (default). Week 10
=WEEKNUM(A2,2) Number of the week in the year that 3/9/2012 occurs,
based on a week beginning on Monday (the second argument, 2). Week 11




Hope this helps

[uboche]

Sorry, editing mishap.

[uboche]

Formula Description Result

=WEEKNUM(A2) Number of the week in the year that 3/9/2012 occurs,
based on weeks beginning on Sunday (default). Week 10
=WEEKNUM(A2,2) Number of the week in the year that 3/9/2012 occurs,
based on a week beginning on Monday (the second argument, 2). Week 11
Hope this helps

Please note that when you need week numbers for use in Europe, WEEKNUM(A2,2) will give you incorrect results for years when January 1st falls on a Friday, Saturday, or Sunday.
You need to use WEEKNUM(A2,21) instead. Function code 2 does not provide correct ISO week numbers in these years.

I'm sorry I didn't quote the Excel help comprehensively enough. In "System 1" as mentioned in my previous posting, week1 is always the week that contains January 1st. That's what is used in the US. In "System 2", the system used by ISO, the week containing January 1st is week 1 only if January 1st occurs no later than Thursday. Otherwise, it's week 53 of the old year and week 1 is the following week.

WEEKNUM(A2,2) uses Sytem 1 while WEEKNUM(A2,21) is using System 2. Check Excel help if it's still unclear.

[royaltiger]

=INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)

The formula above is for anyone who uses Excel 2007 0r under as the function =Weeknum(A1,21) doesn't recocognise the "second system" or iso function used for uk, it only works on Excel 2010 and upwards.

[aimeusdietger]

This is quite insightful. After carrying out the search for the previous post I used the complex answer first. However, I got mixed up after a while and opted to restart using the simple method. It worked out quite well. I'm a bit green in this.
these guys sell Ultra Heavy Duty Drawer Slides | Check This glissiere telescopique forte charge Out
click this Texas County GIS Data here now | read this storage units south auckland post