Help With Time Line using Scatter Plot

[Izhar]

Folks,

i need your help with the attached file to develop and format a Time Line using Scatter Plot

please see the attached file and help me make a time line with Item description

many thanks,
Izhar


Post Moved by Mod

 

[Luke M]

How about creating a psuedo Gantt chart?



Created by using a stacked bar chart, and then a dummy XY series that runs along the bottom and has x-error bars.

If not, perhaps you can mock up something showing what you would like to see?

 

[Izhar]

Thank you for reply
i wanted to do this https://www.officetimeline.com/excel-timeline
however i am using excel 2010, which does not have give me the options to edit my scattor plot to get the results i want

i am sttudying your chart in the mean time if it is a better soloution

 

[Luke M]

You could make that, but it would be very cluttered. Your example would have 36 items on chart, all within a very close time frame. IMO, the gantt view is cleaner.

 

[Izhar]

i agree

can you please walk me through as to how to create a gantt chart with the help of screen shots?

in the sample file i tried modifying the x axis to change the date range with a three day interval but could not figure it out.

thanks for all your help

 

[Luke M]

The x-axis that you see is actually a dummy series, formatted to look like the axis. Data controlling it is in G26:I38. I set up some formulas to find the min/max of your dates. Then, we will in dates going by one month in col H. The values in col G calculated the difference between each date, which will be used as the actual x-value. The 0's in col I are the y-values, telling series to by plotted at y = 0. To change the increment, you would change formula in H28 from this:
=EDATE(H27,1)

to something like this:
=H27+10

Copy down as needed. Note that you may need to extend the series if you have more data points than my chart currently has.



The actual data for the chart is hidden underneath, in G4:I22. This is a stacked bar chart. The new start series figures out how far from starting point on time line (ex Aug 1), we want to start. This is the first bar, which is currently formatted clear. Here's the same chart with that series formatted red.


Delta figures out distance between two dates, which is what controls size of blue bars. In case of 0 days between start/stop, I used a MAX function to make sure a value of 0.1 just so that you can see a tiny sliver.