Results 1 to 14 of 14

20020521, 15:01 #1
 Join Date
 May 2002
 Location
 Wels, Oberösterreich, Austria
 Posts
 64
 Thanks
 1
 Thanked 0 Times in 0 Posts
Graph with Calender Week (Excel 2000 SR1)
Hi Folks!
I'm trying to create a graph in Excel 2000 based on values sorted by calender weeks of a few years. For instance CW51/2002, CW52/2002, CW01/2003 etc.
Now my problem is, when I come to CW52/2002 it should continue with CW01/2003 and the last value for CW52/2002. But the graph starts again from zero and not with the value for CW52/2002. Is this known to anyone else? Please help!
PhilippPhilipp
__________________________________________________ ________
Murphys Law rules the world, bow to it!

20020521, 23:53 #2
 Join Date
 Feb 2001
 Location
 Dallas plus 20 miles or so, Texas, USA
 Posts
 876
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
Can you post a sample of what you are describing? A guess would be that it has something to do with the way the series are ordered, but hard to say without seeing it.

20020527, 09:49 #3
 Join Date
 May 2002
 Location
 Wels, Oberösterreich, Austria
 Posts
 64
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
Thx for your answer! I have new info regarding the problem. I've talked again with the guy who asked me this question beforehand. The real problem is to make Excel figure out calenderweeks from the entered date. For instance if you enter "28.5.2002" it should display CW 22 or something in that way. Sorry for my mistake, hope you can help me with this!
Philipp
__________________________________________________ ________
Murphys Law rules the world, bow to it!

20020527, 09:55 #4
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
IF you're in the US, there is the WEEKNUM function for that, which is part of the analysis toolpak addin (tools, Addins). If yoou have to use the ISO weeknumber, this gets the weeknumber from a date:
=INT((A2(DATE(YEAR(A2+(MOD(8WEEKDAY(A2),7)3)),1,1))3+MOD(WEEKDAY(DATE(YEAR(A2+(MOD(8WEEKDAY(A2),7)3)),1,1))+1,7))/7)+1Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020527, 10:38 #5
 Join Date
 May 2002
 Location
 Wels, Oberösterreich, Austria
 Posts
 64
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
First of all thx for the help guys! Btw I'm here in Austria / Europe so I think I've to use the ISO System. Isn't there any other, "easier" way to solve this problem than this "Monster Formula"?
Philipp
__________________________________________________ ________
Murphys Law rules the world, bow to it!

20020527, 12:00 #6
 Join Date
 Jun 2001
 Location
 Maidstone, Kent, England
 Posts
 398
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
Weeknum function OK here in the UK so it should work for you too.
try the following formula, where C7 is your date cell
="CW"&WEEKNUM(C7,2)&"/"&YEAR(C7)
This will nearly give what you asked for in the first post except that it will not give a 2 digit week below 10! I am not sure how to format it that way with out building a "monster formula" <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
HTH
Peter

20020527, 12:27 #7
 Join Date
 May 2002
 Location
 Wels, Oberösterreich, Austria
 Posts
 64
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
Thx a lot, you saved my day! Only 1 problem left: For instance the 1st of january 2003 should be CW 1/2003. But the same week also contains the 31st of december which is CW 53/2002. Is there a way to tell excel if there is a change in year then start again with CW 1? If you can help me out with this i owe you something! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
Philipp
__________________________________________________ ________
Murphys Law rules the world, bow to it!

20020527, 12:38 #8
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
Please note, that the ISO weeknum *sometimes* is the same as the weeknum the Standard worksheet function WEEKNUM returns, which is AMERICAN.
Check out this site for a full description:
http://www.cpearson.com/excel/weeknum.htmJan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020527, 12:52 #9
 Join Date
 May 2002
 Location
 Wels, Oberösterreich, Austria
 Posts
 64
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
Thx pals! I think that should do it for a while. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
Philipp
__________________________________________________ ________
Murphys Law rules the world, bow to it!

20020527, 13:22 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
In Excel 97, the Weeknum function from the Analysis Toolpak uses the US numbering system: week 1 always starts on the 1st of January. This means that the first and last week of a year usually contain less than 7 days. I don't know if this changed in Excel 2000.
In The Netherlands, where I live, we use the ISO 8601 numbering system:
A week always starts on Monday.
A week is always 7 days long.
Week 1 of any year is the earliest week in which 4 or more days of that year fall (or equivalently, the week in which the 4th of January falls)
In this system, week 1 of a year can start in the previous year  for instance, week 1 of 2002 started on the last day of December 2001, because that was a Monday.
You can "hide" the monster formula by putting it in a userdefined function. This doesn't make it easier, but makes you formulas look more decent.
Unfortunately, the VBA function Format(aDate, "ww", vbUseSystemDayOfWeek, vbUseSystem) has a bug, so we can't use that. (See Q200299) This bug is present in VB 5 (which is behind Office 97 VBA) and VB 6 (behind Office 2000 VBA).
Here is one possible implementation:
Function WeekISO(Datum As Date) As Integer
Dim datJan4 As Date, datWeek1 As Date
Dim intYear As Integer, intDayOfWeek As Integer
For intYear = Year(Datum) + 1 To Year(Datum)  1 Step 1
datJan4 = DateSerial(intYear, 1, 4)
intDayOfWeek = WeekDay(datJan4, vbMonday)
datWeek1 = datJan4 + 1  intDayOfWeek
If Datum >= datWeek1 Then Exit For
Next intYear
WeekISO = (Datum  datWeek1) 7 + 1
End Function
If this function is in a module in the workbook itself, you can use it the same way you use the builtin workbook functions, for instance
If you put it in another workbook, for instance Personal.xls, you need to prefix the function:
=Personal.xls!WeekISO(C7)

20020527, 13:38 #11
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
This formula should give you the two digit weeknumber:
<pre>="CW"&TEXT(WEEKNUM(C7,2),"00")&"/"&YEAR(C7)
</pre>
Legare Coleman

20020527, 13:50 #12
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
Hi Hans,
<<A week is always 7 days long.>>
Hey, why didn't I know that! My boss seems to think it's 9 <g,d&r>Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020527, 15:19 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
Hello Jan Karel,
Of course, a week is 7 days by definition. Even I seem to remember that ... Perhaps I should have said that one "week number" always covers 7 days in the ISO system. This is not the case in the US system  in the attached picture the week number has been computed using the WEEKNUM function from the Analysis Toolpak. Week 1 of the year 2000 lasts only 2 days.
Please keep this thread hidden from the management, before ALL our bosses start to think that a week is 9 days!

20020528, 05:53 #14
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph with Calender Week (Excel 2000 SR1)
Hi Hans,
<<... Perhaps I should have said that one "week number" always covers 7 days in the ISO system. This is not the case in the US system >>
I never grasped why those Americans insist on doing things wrong <g,d&r>.
<<Please keep this thread hidden from the management, before ALL our bosses start to think that a week is 9 days! >>
Yeah, it's bad enough if only my boss thinks that.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association