+ Reply to Thread
Results 1 to 7 of 7

Dynamically change the X Axis containing dates to only include dates in a selected quarter

  1. #1
    Registered User
    Join Date
    09-09-2022
    Location
    Centerville, Utah
    MS-Off Ver
    MS365
    Posts
    4

    Dynamically change the X Axis containing dates to only include dates in a selected quarter

    Hello, I am looking for a solution to my Problem. I have a table with 3 Columns. In Column A are dates that are the beginning of each week since 2020. In columns B and C are Data about Units Produced.

    I am wanting to create a Clustered Column Chart that has the two Units Produced Numbers next to each other on each given date. However, given the number of dates, this gets unreadable pretty quickly.

    I am wondering if there is any solution that would allow me to dynamically set which dates appear on the X Axis of the chart. If this were a Pivot Table, I would just add a timeline, but this does not appear to be a feature for charts. I can also define the beginning and end dates with formulas no problem, but I am not familiar enough with Charts to know how to set the formulas as the Min and Max values for the axis.

    Thank you so much in advance for any insight into the problem!

  2. #2
    Registered User
    Join Date
    09-09-2022
    Location
    Centerville, Utah
    MS-Off Ver
    MS365
    Posts
    4

    Re: Dynamically change the X Axis containing dates to only include dates in a selected qua

    Adding Attachement
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    16,101

    Re: Dynamically change the X Axis containing dates to only include dates in a selected qua

    I'm not sure what kind of solution you are looking for.

    My first thought was to just use filters on the data. First, select the chart and format the chart area so it does NOT move or size with cells and make sure the "hidden and empty cells" settings are to not show data in hidden cells. Then select the drop down arrow next to "beginning date" and use any of the filter options to choose which records to display.

    Assuming your version of Excel supports it, there is the FILTER() function https://support.microsoft.com/en-us/...c-4877ad80c759 that can be used to extract the desired subset of the data, then build the chart on that subset.

    Would either of those options work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    09-09-2022
    Location
    Centerville, Utah
    MS-Off Ver
    MS365
    Posts
    4

    Re: Dynamically change the X Axis containing dates to only include dates in a selected qua

    Thank you for your reply MrShorty.

    The first option using a manual filter on the table will not work unfortunately, as I would like the filter to update automatically as the current date changes.

    I think that the second option may work for me. Filtering Column A where the date is part of the current quarter. I just have to work out the syntax now hahaha.

    Thanks so much!

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    18,401

    Re: Dynamically change the X Axis containing dates to only include dates in a selected qua

    If this were a Pivot Table, I would just add a timeline...
    May I ask why you don't want to use a pivot table and chart? You could set the date filter to this quarter instead of using a timeline to make it rather automatic.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    09-09-2022
    Location
    Centerville, Utah
    MS-Off Ver
    MS365
    Posts
    4

    Re: Dynamically change the X Axis containing dates to only include dates in a selected qua

    Oh my goodness, my apologies. I am still learning all that charts can do. This is the perfect solution. Thank you so much!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    18,401

    Re: Dynamically change the X Axis containing dates to only include dates in a selected qua

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. Dynamically change x & y axis mins and maxs based on dynamically changing data?
    By udf463 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-16-2021, 03:19 PM
  2. Unable to change dates on axis in a gannt chart
    By n.abid in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-26-2021, 08:30 AM
  3. [SOLVED] Why does the graph's time axis not match the dates in the selected data?
    By lovecolorado in forum Excel General
    Replies: 14
    Last Post: 05-07-2019, 10:35 AM
  4. Trying to sort dates to include a range of dates
    By ehh-cellent in forum Excel General
    Replies: 6
    Last Post: 08-28-2017, 04:21 PM
  5. Replies: 2
    Last Post: 06-22-2017, 06:16 PM
  6. Series of Dates With Quarter End Dates
    By whitefeather in forum Excel General
    Replies: 2
    Last Post: 11-24-2016, 08:40 AM
  7. Replies: 18
    Last Post: 03-24-2013, 04:20 PM

Tags for this Thread

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