Weekly Chart With Ability To Change Start Date

  • I have a simple bike log that lists out each day of the month in rows with a summary row between months. Like this...

    January Summary
    Jan 1
    Jan 2
    Jan 3
    Jan 31
    February Summary
    Feb 1
    Feb 2
    Feb 3
    Feb 28

    and so on. Each column has a data field where I enter in miles and distance and so on. I frequently add rows if I do two rides in a day so two rows may start with "January 23rd". I would like to have a chart that shows me my weekly ride summary. But I have some issues:
    1. With the field summary I don't know the best way to do a chart that doesn't include the summary (the summary data would throw the chart way off)
    2. If I have multiple rides in a day I don't know how to make the week include those days
    3. I may want the week to start on Monday and not Sunday. Is it possible to have the user choose and have the chart change automatically?

    Lots of stuff here. I hope it makes sense.


  • Re: Weekly Chart With Ability To Change Start Date

    No time for all your questions, sorry. But to make a Dynamic Named Range to include only 1 week from a starting date of choice, do this.

    1) Use any cell to hold a Data Validation List of valid dates and then Name this cell StartDate

    2) Create a Dynamic Named Range for the chart date Series and link it to the StartDate. E.g.


    Or, you could try a PivotChart.

  • Re: Weekly Chart With Ability To Change Start Date

    Thanks for your reply. I think your solution only shows the sum from one date down seven days. That will work if I am only trying to sum 7 days but there are two problems.

    1. I have to do every seven days through the year (weekly chart for entire year)

    2. If another day is added to the week will this expand to include that new day?

    I may be implementing it wrong so your solution may be the right one with my mistakes.


  • Re: Weekly Chart With Ability To Change Start Date

    The Dynamic Named Range will ensure it expands the date series when new dates are added. The named validation list cell will ensure your chart series shows one week FROM the date chosen.

    Like I said, you maybe better off with a PivotChart linked to a standard Dynamic Named Range.

  • Re: Weekly Chart With Ability To Change Start Date

    I agree wuth Dave that a Pivot Chart based on dynamic named range is probably the best solution. But if you do not want to use a Pivot chart, then take a look at these tutorials on creating dynamic and interactive charts

  • Re: Weekly Chart With Ability To Change Start Date

    Thanks for your messages. I have avoided using Pivot Tables in the spreadsheet because of the problems when people with Macs try to use them. I may revisit that idea and see if Macs still have difficulties with pivot tables.

    Another idea is to make the weeks always start on Monday and just hardcode the data as two separate colums (one for date and one for mileage summary). It is a work-around and not my first choice. I would rather do this with smart coding.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!