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.
look at heavy duty drawer slides full extension here | discover our heavy duty drawer slides full extension post | https://www.gsf-promounts.com/full-exte ... er-slides/ | try these title 24 companies out | look at this Title 24 report website | do you know Texas parcel data | https://www.youtube.com/watch?v=cIR1dfB2p20 | https://www.youtube.com/watch?v=iwIAZKS9t1o |
| view black elevator shoes for men homepage | https://www.guidomaggi.com/us/men/boots/ | find out more on scarpe con rialzo uomo here

[jorgepp]

Hello all!

This has been discussed earlier, so just search for "Week of Year" and you will find a very complicated answer and a simple, though not so accurate an answer. That as I recall is simply days of year / 52 = week number of year and then take the whole number.

greetings,

[Frezatul]

Hi all, interesting topic. I'm just new here and i'm trying to learn more!!