• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Timeline and monthly X axis...

L

Lolo

Member
Hello,

I try to create a dynamic timeline in Excel (see Excel file in attached).
like described in the image in attached

All is quite fine, but the main remaining issue is about the X axis labels.
I use an XY scatter chart to plot events, but I'm unable to create an X-Axis by month.

I have searched on internet, they advise to use a line chart, in order to better manage datalabels, but I don't manage to do what I want neither with XY scatter chart neither with line chart.

thank for your help.

NB: My chart is based on a table where events (1 line = 1 event) are filled in. The user can decide to show an event or not on the chart by put an "X" on column 'DISPLAY_ON_TIMELINE'
NB2: The user can choose to see FINISHED or PLANNED events by using the combobox in A6
NB3: There is a little macro to "manually"update labels (event name + date (optionnal)) since I'm in Excel 2010, not 2013 :(
 

Attachments

  • Result.jpg
    Result.jpg
    14.7 KB · Views: 9
  • SuiviTemps_TIMELINE_SAMPLE.xlsb
    43.8 KB · Views: 7
Hi Lolo,

When we can't format the axis the way we want, let's make our own! :cool:

By that I mean, we can add a new series to the chart and give it the apperance of a axis. On your CALC sheet, I found the Min/Max date of values being plotted. I then calculated out when the first of each month should fall in a range that covers those dates. I add this as a series, with a y-value of 0.
Format the series to no line, no markers. Add a negative y-error bar to give apperance of a little tick.
Format the native x-axis to show no tick marks and no labels.

Voila! You've got a chart now with x-labels for the 1st of each month. :)
upload_2015-7-27_13-16-14.png
 

Attachments

  • SuiviTemps_TIMELINE_SAMPLE LM.xlsb
    45.4 KB · Views: 18
Thank you for your prompt answer !
It works fine:)

I thought to this solution, but i was too focused on trying to use line chart and an "obvious solution". Thank you for unblocking me.

I will add now a new serie to manage a vertical dash-line for representing today,
and will update dynamically labels, instead of clicking on button, and it will be fine :)

I also want to add X error bars to represent duration/completion of tasks, like in a gantt chart (with an otpion to display or not these horizontal bars), and my timeline will be OK !

Thank you a lot (Jedi/Ninja) Luke
 
You must log in or register to reply here.
Back
Top