Date formatting for X-axis

[Tarnathan]

Hi all,

After having spent a few hours on this stupid problem I decided to post something here to request your help.

I'm trying to build a timeline with our project date (in a stacked bar chart).

Here are my data :
\1

Here is the current chart :
\1

My issue is with the label of the X-Axis (dates).
I would like to show there only 1st of each month.

I don't know why but I can't change the axis type to date in the axis options.
Then I'm not allowed to have date units option (to set a first value to 1/10/16 and unit of 1 month).

Here are the option of the X-axis
\1

Could you please help me understanding what's wrong ?
It is slowly driving me crazy

Thanks in advance !

[MrShorty]

You appear to have a bar chart, and a bar chart's horizontal axis is a value axis (one usually thinks of the horizontal axis of a bar chart as the Y axis), and value axes do not have a "date" axis option.

You will probably need to create a bar + line/column combination chart in order to get the effect that you want. You will need to create a suitable series in the spreadsheet, add that to the chart, then change the chart type for this secondary "dummy" series to line/column. Then you can use the horizontal axis associated with the secondary series to create the horizontal date axis that you are looking for.

Can I assume that you are familiar with the steps to create a bar + line/column combination chart?

[Tarnathan]

Thanks A LOT for this feedback ! Now I understand better why I wasn't able to change the axis type.

I have to say that I'm not familiar with these steps but I was looking for a pretty simple/straightforward solution to show our planning with a nice visual.
Isn't there any Excel chart that cover this need (I'm not blocked with bar char)

Thanks in advance AGAIN !!!!

[MrShorty]

I am not aware of any built in chart types that will build a "Gantt chart with a date axis along the horizontal axis".

[Tarnathan]

Ok, thanks for the feedback.

Could you please provide me a few tips for the bar + line/column combination chart ?

Thanks

[Tarnathan]

Oops : double posting

[MrShorty]

Here's how I would expect to proceed (starting with the current chart):

1) Select the start and duration columns.
2) Copy to clipboard -> select chart -> paste special -> as new series with x values in first column
3) select new series -> chart type -> change chart type to line
4) If Excel does not automatically add both vertical and horizontal secondary axes, add those.
5) Format axes as needed
6) Hide "dummy" series and extra axes, as desired

Where do you get stuck?

[Tarnathan]

To be perfectly honest, I'm really sorry but I don't understand the idea of the solution.

I don't get what you mean by "paste special -> as new series with x values in first column".
When I convert the graph into "line" it doesn't correspond anymore to what I'm trying to do.

I'm a bit lost ...

If it's too complex to explain, forget about it, I'll do that manually (it will be ugly and not efficient at all but I'm tired to spend time on this ridiculous task !)

[MrShorty]

I don't get what you mean by "paste special -> as new series with x values in first column".

This is my recommendation for adding the "dummy" series that will be used to create the secondary axis system. After clicking "copy" for the columns selected in step 1, you use the paste special command to add these columns to the chart.

When I convert the graph into "line" it doesn't correspond anymore to what I'm trying to do.

When creating a combination chart, you don't change the chart type of the entire chart, only change the chart type for one of the series (in this case, the dummy series added in the previous step).

Perhaps the another way to illustrate this is to use this tutorial for a bar + XY scatter combination chart. http://peltiertech.com/Excel/Charts/BarLineCombo.html After following all of the steps in the tutorial to create the bar+scatter combination chart, you can then change the scatter chart series's chart type to line, then format the secondary horizontal axis as needed to get the desired horizontal date axis.

[Tarnathan]

Thanks a lot for the details
I'll test that with the tutorial ...

[Andy Pope]

If you don't mind changing the layout of your data you can do it with a line chart. It is still convoluted though.

Change data layout.
create line chart
reverse order of value axis
change number format of value axis to get textual description. If your real labels are not step 1, step 2 etc. then you will need to use a compound chart in order to label that axis.

Use select data dialog to set connection of points with line.
format the lines to be same colour, thicker and with flat ends

[Tarnathan]

Thanks a lot for the all in solution.
I keep it should I struggle with the other one suggested.
Great community, thanks guys !