| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weeknum(today()) gives wrong weeknummer
Hello Excel-lent users,
I use Weeknum(today()) to determine which weeknumber is valid for today. The problem is that for today 11 feb 2010 (02/11/2010 in us date config) it returns with weeknumber 7 instead of week number 6. The same is applicable if I use weeknum(A1) (where A1 eq today()) as well as weeknum(A1;1) and weeknum(A1;2). Can I rely on using weeknum(today())-1 or should I do something else? Please help ! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weeknum(today()) gives wrong weeknummer
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. -- Gary''s Student - gsnu201001 "The Fool on the Hill" wrote: Hello Excel-lent users, I use Weeknum(today()) to determine which weeknumber is valid for today. The problem is that for today 11 feb 2010 (02/11/2010 in us date config) it returns with weeknumber 7 instead of week number 6. The same is applicable if I use weeknum(A1) (where A1 eq today()) as well as weeknum(A1;1) and weeknum(A1;2). Can I rely on using weeknum(today())-1 or should I do something else? Please help ! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weeknum(today()) gives wrong weeknummer
Hi,
Is giving you 7 because is taking in consideration January 1 as a week, the excel help says Returns a number that indicates where the week falls numerically within a year. Important 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. Syntax Please it this helps click yes thanks "The Fool on the Hill" wrote: Hello Excel-lent users, I use Weeknum(today()) to determine which weeknumber is valid for today. The problem is that for today 11 feb 2010 (02/11/2010 in us date config) it returns with weeknumber 7 instead of week number 6. The same is applicable if I use weeknum(A1) (where A1 eq today()) as well as weeknum(A1;1) and weeknum(A1;2). Can I rely on using weeknum(today())-1 or should I do something else? Please help ! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weeknum(today()) gives wrong weeknummer
It makes sense to me. For more information, see:
http://www.cpearson.com/excel/WeekNumbers.aspx -- Gary''s Student - gsnu201001 "The Fool on the Hill" wrote: Hello Gary's Student, The first three days of the year where fri 1st of jan till Sat 3 januari. According to the european standard, this means that week 1 is actually 2. Hence adding -1 in the formula makes sense (here in europe) ? Thanks 4 your answer. Kind regards, Jan-Willem "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu201001 "The Fool on the Hill" wrote: Hello Excel-lent users, I use Weeknum(today()) to determine which weeknumber is valid for today. The problem is that for today 11 feb 2010 (02/11/2010 in us date config) it returns with weeknumber 7 instead of week number 6. The same is applicable if I use weeknum(A1) (where A1 eq today()) as well as weeknum(A1;1) and weeknum(A1;2). Can I rely on using weeknum(today())-1 or should I do something else? Please help ! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weeknum(today()) gives wrong weeknummer
See also
http://msdn.microsoft.com/en-us/library/bb277364.aspx -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gary''s Student" wrote in message ... It makes sense to me. For more information, see: http://www.cpearson.com/excel/WeekNumbers.aspx -- Gary''s Student - gsnu201001 "The Fool on the Hill" wrote: Hello Gary's Student, The first three days of the year where fri 1st of jan till Sat 3 januari. According to the european standard, this means that week 1 is actually 2. Hence adding -1 in the formula makes sense (here in europe) ? Thanks 4 your answer. Kind regards, Jan-Willem "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu201001 "The Fool on the Hill" wrote: Hello Excel-lent users, I use Weeknum(today()) to determine which weeknumber is valid for today. The problem is that for today 11 feb 2010 (02/11/2010 in us date config) it returns with weeknumber 7 instead of week number 6. The same is applicable if I use weeknum(A1) (where A1 eq today()) as well as weeknum(A1;1) and weeknum(A1;2). Can I rely on using weeknum(today())-1 or should I do something else? Please help ! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weeknum(today()) gives wrong weeknummer
Hi,
WEEKNUM provides american week number. Unfortunately, this is different from the european week number for about every second year, e.g. 2010 and 2011. This formula provides european week number: =WEEKNUM(A1;2)-1*(WEEKNUM(DATE(YEAR(A1);1;4);2)=2) Sample: 01.01.2010 results in 0, 04.01.2010 results in 1. Alternative solution to work with overlapping first week, i.e. avoidung the value 0 for the very first days of a year: =TRUNC((A1-WEEKDAY(A1;2)-DATE(YEAR(A1+4-WEEKDAY(A1;2));1;-10))/7) Sample: 01.01.2010 results in 53, 04.01.2010 results in 1. Best regards, Karsten On Thursday, February 11, 2010 8:05 AM The Fool on the Hill wrote: Hello Excel-lent users, I use Weeknum(today()) to determine which weeknumber is valid for today. The problem is that for today 11 feb 2010 (02/11/2010 in us date config) it returns with weeknumber 7 instead of week number 6. The same is applicable if I use weeknum(A1) (where A1 eq today()) as well as weeknum(A1;1) and weeknum(A1;2). Can I rely on using weeknum(today())-1 or should I do something else? Please help ! On Thursday, February 11, 2010 8:26 AM Gary''s Student wrote: 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. -- Gary''s Student - gsnu201001 "The Fool on the Hill" wrote: On Thursday, February 11, 2010 8:28 AM Eduardo wrote: Hi, Is giving you 7 because is taking in consideration January 1 as a week, the excel help says Returns a number that indicates where the week falls numerically within a year. Important 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. Syntax Please it this helps click yes thanks "The Fool on the Hill" wrote: On Thursday, February 11, 2010 9:14 AM Gary''s Student wrote: It makes sense to me. For more information, see: http://www.cpearson.com/excel/WeekNumbers.aspx -- Gary''s Student - gsnu201001 "The Fool on the Hill" wrote: On Thursday, February 11, 2010 11:19 AM Ron de Bruin wrote: See also http://msdn.microsoft.com/en-us/library/bb277364.aspx -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Weeknum | Excel Worksheet Functions | |||
| Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
| IF TODAY equals date in cell A10, or if TODAY is beyond that date | Excel Worksheet Functions | |||
|
=IF(OR(TODAY() |
Excel Discussion (Misc queries) | |||
| Custom Format for WEEKNUM(Today()) | Excel Discussion (Misc queries) | |||
Linear Mode
