Results 1 to 14 of 14
  1. #1
    Star Lounger
    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!

    Philipp
    Philipp
    __________________________________________________ ________
    Murphys Law rules the world, bow to it!

  2. #2
    5 Star Lounger
    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.

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

  4. #4
    Platinum Lounger
    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 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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Star Lounger
    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!

  6. #6
    3 Star Lounger
    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

  7. #7
    Star Lounger
    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!

  8. #8
    Platinum Lounger
    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.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Star Lounger
    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!

  10. #10
    Plutonium Lounger
    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 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. #11
    Uranium Lounger
    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

  12. #12
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Plutonium Lounger
    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!
    Attached Images Attached Images

  14. #14
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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