Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weeknum function issue
Weeknum does not show 53 for the entire week of 12/28/08-1/3/09, it shows
week 53 for 12/28-12/31, and week 1 for 1/4-1/3/2008. The week number can't be both 53 and 1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weeknum function issue
That is the way it has been designed. Following is from the help for WEEKNUM;
"" The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard." "WS" wrote: Weeknum does not show 53 for the entire week of 12/28/08-1/3/09, it shows week 53 for 12/28-12/31, and week 1 for 1/4-1/3/2008. The week number can't be both 53 and 1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weeknum function issue
Hi,
Straight from Excel Help The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard. Mike "WS" wrote: Weeknum does not show 53 for the entire week of 12/28/08-1/3/09, it shows week 53 for 12/28-12/31, and week 1 for 1/4-1/3/2008. The week number can't be both 53 and 1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weeknum function issue
For the result I think you expect try this
=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+ WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7) Mike "Mike H" wrote: Hi, Straight from Excel Help The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard. Mike "WS" wrote: Weeknum does not show 53 for the entire week of 12/28/08-1/3/09, it shows week 53 for 12/28-12/31, and week 1 for 1/4-1/3/2008. The week number can't be both 53 and 1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weeknum function issue
WS wrote:
Weeknum does not show 53 for the entire week of 12/28/08-1/3/09, it shows week 53 for 12/28-12/31, and week 1 for 1/4-1/3/2008. The week number can't be both 53 and 1 You could use something like this, which pulls the WEEKNUM for every day from the preceding Sunday: =WEEKNUM(A1-WEEKDAY(A1)+1) Keep in mind that there will be no week number 1 for any year that doesn't start on Sunday. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weeknum function issue
Hi WS
See this page for more info http://www.rondebruin.nl/weeknumber.htm Or this MSDN page http://msdn.microsoft.com/en-us/library/bb277364.aspx -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "WS" wrote in message ... Weeknum does not show 53 for the entire week of 12/28/08-1/3/09, it shows week 53 for 12/28-12/31, and week 1 for 1/4-1/3/2008. The week number can't be both 53 and 1 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weeknum function issue
Hi
I have an similar problem. However, I also want to display the year, including the week number. My problem is; 31.12.09 = week 53 year 2009 01.01.10 = week 53 year 2010 But I want the entire week from 28.12.09 - 03.01.10 to be displayed as week 53-2009. I hope anyone can help me on this matter. "WS" wrote: Weeknum does not show 53 for the entire week of 12/28/08-1/3/09, it shows week 53 for 12/28-12/31, and week 1 for 1/4-1/3/2008. The week number can't be both 53 and 1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weeknum function issue
On Wed, 4 Feb 2009 02:05:03 -0800, Kriss
wrote: Hi I have an similar problem. However, I also want to display the year, including the week number. My problem is; 31.12.09 = week 53 year 2009 01.01.10 = week 53 year 2010 But I want the entire week from 28.12.09 - 03.01.10 to be displayed as week 53-2009. I hope anyone can help me on this matter. If I understand you correctly, it seems you want to calculate the weeknumber according to the ISO standard, which has week 1 starting on the Monday that includes the first Thursday of the year. The Excel WEEKNUM function does not make that calculation. You can do it with a VBA UDF. To enter this, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this, enter a formula of the type =ISOWeeknum(cell_ref) into some cell, where cell_ref contains a date. Since you don't write how you want to display the year in this message, I can't help you with that. But consider how you want to display the year for 01.01.10 =========================================== Function ISOWeeknum(d1 As Date) As Integer ' Daniel Maher Dim d2 As Long d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3) ISOWeeknum = Int((d1 - d2 + Weekday(d2) + 5) / 7) End Function ========================================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weeknum Issue | Excel Worksheet Functions | |||
Weeknum function | Excel Discussion (Misc queries) | |||
weeknum function | Excel Worksheet Functions | |||
=WEEKNUM function | Excel Worksheet Functions | |||
WEEKNUM Function | Excel Worksheet Functions |