Week Numbers of the Year

For HanDBase related topics that are not specific to one platform.

Re: Week Numbers of the Year

Postby curtterp » Fri Nov 11, 2016 10:45 pm

uboche wrote:
curtterp wrote: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
Have a good day

Curt
I am not a DDHSoftware employee, just a long time HanDBase user.... from Palm to Windows Mobile to Android, to iOS. Thanks to DDH, the database files transferred to each platform without a problem.
curtterp
 
Posts: 681
Joined: Tue May 26, 2009 6:41 pm
Location: Northern IL

Re: Week Numbers of the Year

Postby uboche » Sat Nov 12, 2016 7:45 am

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.
Regards,
Ulrich Boche
uboche
 
Posts: 130
Joined: Wed May 20, 2009 1:52 pm

Re: Week Numbers of the Year

Postby curtterp » Sat Nov 12, 2016 8:39 am

uboche wrote: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 :D
Have a good day

Curt
I am not a DDHSoftware employee, just a long time HanDBase user.... from Palm to Windows Mobile to Android, to iOS. Thanks to DDH, the database files transferred to each platform without a problem.
curtterp
 
Posts: 681
Joined: Tue May 26, 2009 6:41 pm
Location: Northern IL

Re: Week Numbers of the Year

Postby royaltiger » Mon Nov 14, 2016 11:35 am

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
royaltiger
 
Posts: 11
Joined: Wed Mar 16, 2016 6:29 am

Re: Week Numbers of the Year

Postby uboche » Mon Nov 14, 2016 1:50 pm

Sorry, editing mishap.
Last edited by uboche on Mon Nov 14, 2016 2:13 pm, edited 1 time in total.
Regards,
Ulrich Boche
uboche
 
Posts: 130
Joined: Wed May 20, 2009 1:52 pm

Re: Week Numbers of the Year

Postby uboche » Mon Nov 14, 2016 2:12 pm

royaltiger wrote: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.
Regards,
Ulrich Boche
uboche
 
Posts: 130
Joined: Wed May 20, 2009 1:52 pm

Re: Week Numbers of the Year

Postby royaltiger » Fri Dec 23, 2016 7:05 am

=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.
royaltiger
 
Posts: 11
Joined: Wed Mar 16, 2016 6:29 am

Re: Week Numbers of the Year

Postby aimeusdietger » Tue Feb 07, 2017 7:34 am

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
aimeusdietger
 
Posts: 1
Joined: Fri Feb 03, 2017 5:27 am

Previous

Return to HanDBase General Discussion

Who is online

Users browsing this forum: No registered users and 2 guests