+ Reply to Thread
Results 1 to 4 of 4

X-Axis calendar week and year for multiple years

  1. #1
    Registered User
    Join Date
    06-13-2017
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    2

    X-Axis calendar week and year for multiple years

    Hello,

    I'd like to change the x-axis with values from the table to generate a Gantt chart.
    I have no problems to create a chart with for example a range from calendar week 20-51.

    Please Login or Register  to view this content.
    I have added some rows to help me to scale the axis (J7 and J9 / K7 and K9).
    In those cells, I search for the lowest and highest date and change it to a calendar week.

    I also changed the numberFormat to "0 KW" KW means calendar week in German.

    Please Login or Register  to view this content.
    It is working in a range of one year, but as soon as I switch to another year it plots the graph wrong (the reason is obvious week: 42 - 15 makes no sense with this logic right now).

    Is there a way to combine week and year and use this to plot the axis (with VBA, because it should be done automatic every time)?

    I added two files, one is working because the range is in between 1-52 and the other isn't working because it switches from 2016-2017-2018.

    Additional information:
    - Rows A:I are automatically generated (don't touch those please)
    - I can add as many "helping" rows as I want.
    - Files are in German, VBA in English, if you don't understand something then ask me.

    Kind regards,

    Niko
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: X-Axis calendar week and year for multiple years

    I am not sure I understand the question.

    My first action on not-working file, seeing that it is trying to plot up to almost 400 weeks (See R10), was to format the horizontal axis so that the min, max, and major unit were auto. This gives a 0 to 400 KW axis, and the Gantt chart looks like it should. Is your question as simple as that -- how to expand the axis to 400 weeks?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-13-2017
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    2

    Re: X-Axis calendar week and year for multiple years

    I am not trying to plot 0-400 weeks.
    I want to split those in 1-52/2016 1-52/2017 and so on.
    It would be nice if the axis would be like
    2016 | 2017
    1..52 | 1..52

    Does this makes it clearer?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: X-Axis calendar week and year for multiple years

    I think it makes it clearer, though I am not sure exactly how you would want to approach this.

    My first idea is to abandon the "calendar week" numbering and simply use Excel's built in calendar/date serial number system. Whether or not this is suitable will depend almost entirely on whether or not you are flexible on using calendar weeks as your "base unit". Steps I followed:

    1) Remove WEEKNUM() function from N7 [=IF(B7="","",(H7))] and copy new formula down.
    2) Remove "/7" in formula in O7 [=IF(B7="","",P7)] and copy new formula down.
    3) Select horizontal axis in chart -> Format axis ->
    3a) Minimum value is 42370 (Put 1/1/2016 into a cell and format as "number->0 decimal places" to get serial number for this date)
    3b) Maximum value is auto
    3c) Major unit is some multiple of 7 (I used 70)
    3d) Minor unit is 7
    3e) Number format on axis is some date/month/year format.
    4) Other formatting and edits as needed

    It does not result in the "year + weeknumber" kind of format you asked for, but it easily displays the bars of the Gantt chart so that one can see where they fit on the calendar.

    I can think of another option that is more consistent with your "year + weeknumber" request. It involves adding another data series to create a stacked bar + line combination chart, and using the resulting horizontal axis for the secondary "line" chart horizontal axis to get the effect. I think it will be more difficult to set up and maintain, but it is doable. If you are not flexible about using "weeknumber" as your base unit, then we can explore this possibility.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 01-13-2017, 04:57 PM
  2. [SOLVED] Formula that can calculate Year to Date % success across multiple years
    By abreet in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-23-2015, 11:30 AM
  3. [SOLVED] Growth function, percent per year over multiple years
    By Speshul in forum Excel General
    Replies: 9
    Last Post: 01-26-2015, 06:24 PM
  4. Replies: 6
    Last Post: 05-08-2013, 01:46 PM
  5. Year To Date Filter - Multiple Years
    By andyrow in forum Excel General
    Replies: 4
    Last Post: 01-11-2013, 09:03 AM
  6. Replies: 3
    Last Post: 03-09-2009, 09:00 AM
  7. same month/day on x axis for multiple years
    By mjo73 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-15-2006, 02:25 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1