Secondary Horizontal date axis

I don't know if this is what you want, but I selected the Axis and clicked on the buttons to reset the Minimum and Maximum and Major Units.

I am not certain what you are trying to do here, so I'm not sure how helpful I can be.

I note that your secondary axis is formatted with a minimum of 0 and a maximum of 1. The underlying values/serial numbers behind those dates will be 42000+, so there is no way that those dates will be correctly displayed on a 0 to 1 scale. The first adjusment I would make would be to set the maximum and minimum of the secondary axis to auto or to something more reasonable for the dates you are trying to display. You might go into the range with the dates and delete the number format so you can see what the actual serial numbers are for those dates, so you can choose better values for the axis max and min and divisions.

I would guess that there is a lot more you want to do here. If you can explain what you want to do, we may be better able to help you.

Set the axis limits to min 42443 (the serial number for 3/14/2016), max 42453 (serial number for 3/24/2016), and major unit 1? As I suggested , clearing number/date format so you can see the underlying serial numbers is useful here.

I generally don't like converting numbers/dates to text. I cannot visualize what you are trying to do here, but, in a situation like this, I will often try to see how Excel's automatic axis limits work out. If they really don't work out, then I will usually just manually adjust the axis limits for each data set (it doesn't take long).

I have seen VBA procedures that can be used to automate axis limits using your own algorithms. Let me know if you would want to go down that rabbit hole.

In this case, it might be useful to convert the secondary series and axis system to a column chart instead of a bar chart. Then, add the secondary horizontal axis and format it as a text (rather than date) axis. That might be worth trying to see what happens and how that could be adapted. Is that what you are thinking of?

Change the second series to column chart.
Delete secondary vertical axis
Add secondary horizontal axis, format to cross on tickmarks and display text

The dates will only line up though if you have match dates for each percent band.

** attachment remove **
see newer post for correct file

Here’s my latest shot. It uses a change event on the Input Data page to set the minimum and maximum date for the axis based on the min and max dates in the data.

I have a couple of helper cells on the Parameters page to collect these dates. The parameters page can be hidden.

Is it possible for the secondary horizontal axis to use text?

On a bar chart, the horizontal axes are value axes, so they do not know how to use text.

The main technique I'm aware of for putting text on a value axis is to use a dummy series and data labels: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html The tutorial is for a column chart, but should be essentially the same process for a bar chart.

You say that you have a user who has been doing this for 20 years on an older version of the worksheet. I would be curious how he/she has been maintaining/formatting the secondary axis.

Oops. I meant to delete all those other modules. They were recorded macros so I could figure out the syntax. What runs the show is the change event on the input sheet.

Please Login or Register  to view this content.

I set it up this way so that when dates are added or changed, the macro is kicked off. I gave a static name to the two cells on the Parameter pages and read them into the code. VBA doesn't have a native Min or Max function. I could have written one, but Excel has a built in one so I used it instead.

I see I attached the file for a different problem.

See this one where the column chart axis displays the dates.