# Thread: Graph with Calender Week (Excel 2000 SR1)

1. ## 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!

Philipp

2. ## 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.

3. ## 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!

4. ## 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 add-in (tools, Add-ins). If yoou have to use the ISO weeknumber, this gets the weeknumber from a date:

=INT((A2-(DATE(YEAR(A2+(MOD(8-WEEKDAY(A2),7)-3)),1,1))-3+MOD(WEEKDAY(DATE(YEAR(A2+(MOD(8-WEEKDAY(A2),7)-3)),1,1))+1,7))/7)+1

5. ## 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"?

6. ## 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

7. ## 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>

8. ## 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.htm

9. ## 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>

10. ## 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 user-defined 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 built-in 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)

11. ## 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>

12. ## 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>

13. ## 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!

14. ## 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•