How To Create A Time Line Chart

[CraigB02]

I am trying to create a line chart with time lines (please see attached). I have tried the various charts in Excel 2016 but cannot find one that will create this. Does not have to be exactly like the example. In a worksheet a column for Block No., Start Time, End Time, and hours (calculated end time minus start time).

Any ideas would be greatly appreciated.

Thanks,
Craig

Time Chart.jpg

[FDibbins]

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[MrShorty]

My first impression of your chart is that it could be made as a Gantt chart: https://support.office.com/en-us/art...7-F0FB34D9E2B6
A different approach: https://www.excel-easy.com/examples/gantt-chart.html

Study those tutorials to understand how to make a Gantt chart. Make sure your times are entered as real time values and not text strings. Then get your data arranged in the spreadsheet and add a Gantt chart/stacked bar chart.

[CraigB02]

Attached is a small workbook with some data for the chart that I am trying to achieve.

Thanks,
Craig

[CraigB02]

Mr Shorty,

Thanks for the links. I did review the information and also built the Build A House chart. I then tried to modify it with the times and I could not get it to display the data.

Thanks,
Craig

[MrShorty]

Your sample file does not include any of your attempts to create the chart, so I don't know what you have tried and what has not worked.

Here's what I did -- recognizing that my older version of Excel does not have the built in Gantt chart option, so I used a stacked bar chart:

1) Add a column to calculate duration =C4-B4, copied down.
2) Select the block of data A3:D18 and insert a stacked bar chart.
3) clear the "End time" series.
4) Format chart elements as desired

Is that what you are trying to do?

[CraigB02]

That is pretty much what I was looking to do. I was also able to show the split shifts and added the names. But what I cannot figure out is how to mike the times in either 30 minute or 60 minute intervals. I tried playing with the Axis Options, Bounds and Units information, but could not get it to display by 30 or 60 minutes.

Thanks,
Craig

[MrShorty]

If I understand your question, I expect it is because you are not familiar with how Excel stores times (and dates). Something like this should help: http://www.cpearson.com/Excel/datetime.htm

Once you understand that time is just a fraction of a day, then a 1 hour increment is 1/24 of a day or 0.0416667. A half hour increment is 0.5/24 or 020833333. Enter those values (or other suitable value) for your major and/or minor unit values.

[CraigB02]

I did some research and changed the values accordingly, I even made a cheat sheet of what each minute is for the decimal equivalent. I changed the bar to show the duration and now it displays as expected with the times shown every 60 minutes. Some of the work pieces may be a split shift. In the example 110-3 starts at 5:05 and ends at 9:25, then starts the second piece of work at 14:00 for 3 hours to 17:00. This second piece I can only get to display correctly if I put in the start time as 0:15 (ending 3:15, duration 3:00), it displays correctly on the graph as starting at 14:00 and ending at 17:00. If i use 14:00 in thedata, it displays on the following day.

Thanks,
Craig

[MrShorty]

When I open your latest file and pull up the Select Data dialog/pane, I see that you have 2 copies of each "time" series, but I don't understand why. So, for the split shift example, your first (invisible) stacked bar is from 0 to 5:05, the second stacked bar is 4:20 long to 9:25, then you have a third (invisible) stacked bar that is another 4:20 long (to 13:45), then a 4th (invisible) stacked bar for the start time of the second part of the shift that is 0:15 long to get to 14:00, and so on.

The answer to your question will depend greatly on why you have the second copy of the time series. If that is a mistake and there should only be one copy of each time series, then you can simply delete/remove the extraneous series. If there is some other reason for having it there, then you will need to work the second shift's start time out that accounts for the reasons for having two copies of the same series in the chart.

[CraigB02]

The second time series is the second half of the split shift.

Craig

[MrShorty]

Next time I get on a computer with Excel, I can look again, but I recall four data series labeled "time" in the chart, two for the first half of the shift in column D and two for the second half of the shift in column G. Have you opened up the select data dialog (or whatever constitutes a list of all the data series in the chart in 2016 to see?

[CraigB02]

Column B (Start Time) is the start of the shift, Column C (End Time) is the end time of the shift, Column D (Time) is the length of the shift, Column E (Start Time) is the start time of the second piece of work if it is a split (as indicated in cell E2), Column F (End Time) is the ending time of the second iece of work, Column G (Time) is the length of the second piece of work. Yes, I have looked at the data selection.

Craig