Show Tasks as Lines and Show Time Points' bars on X-Axis

Hello,

Need Chart.PNG

I want to Show Tasks as Lines, specified with Duration at specified height.

Along with that, I want to show some points of a day viz. Sunrise, Sunset etc Verically as bar on X-Axis.

Both Lines and Bars should reflect Date-Time as specified in Data and not at equivalent distance.


I have figured out exactly what I need in a picture attached.

Any help on this is welcomed.

Thank You

It looks to me like an XY scatter chart should do this just fine. Are you familiar with creating scatter charts (https://support.office.com/en-us/art...5-104A9018B86E )? Since your data includes date/time information, are you familiar with how Excel stores dates/times as serial numbers (http://www.cpearson.com/Excel/datetime.htm )?

Assuming your date/time stamps are true numbers/dates/times (and not text strings), then I would expect to do something like:
1) I need to add the ending date/time and position for the D segment. Enter/calculate the ending time for D in G7 and Enter 20 in I7.
2) Select G3:G7,I3:I7 and insert a scatter chart. Check that Excel created the chart correctly (with column G as the X values and column I as the Y values).
3) Select the data series -> Format series -> somewhere in the format series dialogs (in 2007 it is under "marker fill") is a "vary colors by point" option. You should now have the horizontal, multicolored line that indicates each task.

For the vertical lines, we can add a second data series, add vertical error bars to that series, and format the error bars.
4) This data series will also need an arbitrary height value to position the points in the chart. In D2:D8, enter 30 (or whatever seems appropriate).
5) Select the chart -> bring up the Select Data dialog -> Add data series -> B2:B8 as the X Values for the new series and D2:D8 as the Y values for the new series.
6) Select the new data series and add error bars (In 2007, this is on the Chart -> Layout menu). 2007 will add both vertical and horizontal error bars. Delete the horizontal error bars.
7) Select the vertical error bars -> Format -> negative error bars only and set them to 100% -> other formats as desired.
8) Add data labels to the new data series and enter the appropriate text in those data labels. 2007 does not support "values from cells" for the data labels, so you will need to manually enter the text for each data label, or use an external utility like Rob Bovey's free XY chart labeler add-in (appspro.com ). Newer versions (starting in 2013, I believe) have built in support for data labels from cells.

That should get you something very close to what you show in the picture.

Attachment must not have attached properly. Make sure you are clicking on "go advanced" and then finding the "manage attachments" link to bring up the working file uploader.

1) You did not find a "vary colors by point" option when formatting the data series? Of course, you can always select each data point/segment (click on the series once to select the series, then click on the desired data point to select the point/segment) and bring up the format data point dialog and change line color that way. You will need to repeat the process for each segment. Alternatively, you can add each segment as its own data series. It all depends on exactly what you are trying to do here (and maybe why you are unable to have Excel automatically vary colors by point).

2) Not sure what you mean here (because I can't see the new picture, probably). It suggests that you have the chart formatted to not display the horizontal axis. If that is the case, add the axis back into the chart.

3) A few alternatives other than error bars. Add each desired "error bar color" as a separate data series. Or use start and end points for the vertical lines, which you can then format just like you did for the four horizontal lines the first data series. The basic idea is described here: https://peltiertech.com/Excel/Charts/AddLine.html

Chart type Bars will do the job:
Only the data labels should be fixed

Barchart.JPG

Hi PietBom,

I tried that but seems complicated. Is there any alternate method?

1. I want to Show Tasks as formatable Lines, specified with Duration at specified height.

2. Along with that, I want to show some points of a day viz. Sunrise, Sunset etc Verically as formatable bars on X-Axis.

3. Both Lines and Bars should reflect Date-Time as specified in Data and not equidistant.

Thannks

1) (vary colors by point). I'm not sure I understand exactly what you tried. When I tested it, I created the chart as an XY scatter lines without markers and checked the vary colors by point option, and Excel gave me a different color for each segment. I have never seen that option disappear from the format axis dialog, so I cannot comment on why Excel is not allowing you to format the series the way you describe. The option is there for me in 2007 and gives a result very similar to your desired picture. Since your copy of Excel 2007 (you are using 2007 like your profile indicates, right?) seems to be behaving differently from mine, I'm not sure what recommendation to make.

2) (Axis labels). Somewhere in the format axis dialog is a drop down with your axis labels options. The options are something like "none -- low -- high -- next to axis". Simply choose none for the axis labels.

I have no alternative method at this moment, sorry.
But I modified the tool as you requested.
VBA was used for some effects.