Weeknumbering end of year 2004 start of year 2005

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a small spreadsheet where i use the weeknumbering.
31 december 2004 is on a friday and is in week 53.
1 january 2005 is on a saturday and is in week 1.
Strange, because a week is from sunday till saturday or from monday till
sunday (depents on setting you can use)
monday 3 january 2005 is in week 2.
I use Excel 2000 (Office 2000 SR1 with SP3)

What do I do wrong or is this a bug?
 
Hi


Starter said:
I have a small spreadsheet where i use the weeknumbering.
31 december 2004 is on a friday and is in week 53.
1 january 2005 is on a saturday and is in week 1.
Strange, because a week is from sunday till saturday or from monday till
sunday (depents on setting you can use)
monday 3 january 2005 is in week 2.
I use Excel 2000 (Office 2000 SR1 with SP3)

What do I do wrong or is this a bug?
Click to expand...

This is MS week definition (Bill's standard) :-))

For week starting with Monday and 1st week of year the first one with
Thursday in it I use the formula
=IF(INT((G2-DATE(YEAR(G2);1;1)+1+7-WEEKDAY(G2;2)+4)/7)=0;INT(((DATE(YEAR(G2)
-1;12;31)-DATE(YEAR(G2)-1;1;1)+1)+7-WEEKDAY(DATE(YEAR(G2)-1;12;31);2)+4)/7);
IF(AND(INT((G2-DATE(YEAR(G2);1;1)+1+7-WEEKDAY(G2;2)+4)/7)=53;WEEKDAY(DATE(YE
AR(G2);12;31
);2)<4);1;INT((G2-DATE(YEAR(G2);1;1) +1+7-WEEKDAY(G2;2)+4)/7)))

(Probably you have to replace semicolons with commas. When you need
different week definition, then you have to modify this or to find another
one)
I myself use in my Excel applications the weeknumber calculated by such
formula in format "yyyy.ww" - it can be both number or text. To get
weeknumber in such form I use the formula
=YearNumberFormula & "." & WeekNumberFormula
or
="" & YearNumberFormula & "." & WeekNumberFormula
 
Back
Top