Gantt Chart Help

[Harvey12]

Hi All,

(Just before you read away I do have a version of the graph which contains no sensitive data, which can be sent)

I have created a Gantt Chart which illustrates some project dates and when they are planned to be finished by, the chart also shows their scheduled publish date. Regarding charts and macros it's fair to say I'm a beginner so you may need to talk in simple terms if possible! BUT I have worked with macros previously and done very limited chart work so I do have some understand!

What I am trying to achieve is:

1) Currently the graph date scale is out, by this when looking at the bars, despite for example project 1 being scheduled to finish on 04.01.2016, in the graph it illustrates it looking like the middle of December?

2) I am also trying to make the graph more automatic, by this I mean; there is a key at the bottom of the chart, what I would like is when the projects are registered as complete I would like the bars to colour themselves based on an extra column in a table that the graph plots itself from, is this possible? In addition to this, there are 2 project types, new and refresh, I would like it to be able to colour the bars based on their type.

3) My third and final request is the graph automatically feeds in from a table, this table actually reads from another sensitive data sheet which will not be supplied, but how it get's from the sheet to the table is already set up, is it possible for as the table gets new entries on the bottom, for the graph to automatically pick those up and add them in? I have added 2 extra 'blank' entries to the table for anyone if they think it's possible to do this?

If ANYONE can help me achieve any of the above it would be so very appreciated.

Many thanks in advance
Harvey

 

[Harvey12]

BUMP! Can someone please help

 

[Harvey12]

Some help please guys!

 

[Michael_E]

I had a similar issue creating a Gantt Chart. I ended up coming up with a chart that automatically scaled to the current day and adjusted the markings and annotations to count out 5 years by month and accurately display the duration and start/end time of every project. To address each point.

1: To correct this you need to do some hinky stuff with formulas and converting your dates to numbers. What I ended up doing to make my dates line up is this...(note that my document was a 5 year plan and if yours is shorter term you can adjust the numbers accordingly.)(Also note: the only data that I had to work with was the start date of the project, name of the project, and the estimated end date of the project)

A: double click your horizontal axis and change your axis options to this Min=0, Max=1825(5*365 or 5 years), Major Unit=365, Minor Unit=30.416(The average length of a month) also make sure your axis labels are set to none.
B: You're going to have make some columns, firstly you'll need a duration column so =End-Start.
C: After that you're going to need a column that denotes how long until a project starts I'll call it TimeTil for the rest of this explanation, you'll need to put a =Today() somewhere in your workbook(I couldn't figure out how to make the block itself math out the function). Once you do that you need to a =Start-Today (when referencing the =Today() block, you should add $s into the block address for ease of cutting and pasting A1 becomes $A$1 for instance).
D: Now we have to do a conditional statement based off of those two blocks because of how our axis is configured it will be =IF(TimeTil<0,Duration+TimeTil,Duration). What this will do is make it so that if your project is already ongoing you will get the remaining duration of it instead of the entire duration, we'll call this AdjustedDuration for future reference.
E: Last column, you will need and Offset column to show if a project hasn't started, using another conditional... =IF(TimeTil<0,0,TimeTil). What this will do is add a space before your actual project data series starts, so if a project starts in two months you will have a 60 day blank space before the data set for your project.
F: Right click your chart and click select data, pick your Offset column and then your AdjustedDuration column. Once that's done select your offset data set and make it invisible(no fill/border)
G: The fun part, somewhere on your spreadsheet you're going to need to make some references to dates depending on what labels you want to use, I used labels for every year and every month, if you need more or less adjust this step accordingly. =Today(the cell you already made)+365 gives you a year down the line, after that you can either reference the today block or the block you just did depending on your preference, as many times as you need to.
H: For monthly labels you are going to have to formatting of the cell you want to display, for 3 letter month labels go to format cell and then click the custom category and type in "mmm" into the text bar that would turn the column into Dec for this month. If you want to do a monthly label for each month =Today(again, the cell)+30.4 as many times as you need, as a side note, this does have the possibility of sometimes having the same month twice in a row on a calendar, but that is the price of the calendar dynamically updating.
I: The most annoying part, In your chart you're going to text boxes that reference the dates you just made, for the one directly under the vertical axis type =Today(again, the cell), for each month make a text box and reference the proper month. lining up these text boxes is a pain in the butt.
J: Set up a filter for your data, sorry one last column, I did this because my chart was showing blank entries for projects that had already complete, make a chart that compares today and the end date =IF(Today>EndDate,True), and then filter that column to only show FALSE responses.

2: I'm not sure if that is possible to automatically color specific bars of a data set and not color other bars of the same data set, you could have two charts, one for refresh and one for project but that doesn't sound like that you want.

3: With what I just typed out, you may have to adjust the size of the data set if it's automatically imported into a table. I manually enter the data (it's only a project name and two dates anyways) into my chart and what I did was generate a dummy project till about row 200 and then filter it out, as a rule charts wont show data that is filtered. So to add new projects I just have to remove the filter, paste in what I want, and the replace the filter. Or possibly if you removed the title row from your chart you could select the entire column, but I think that would also cause your chart to generate tens of thousands of gaps.

So this ended up being a really long post, if you want to see what my chart looks like send me a message and I can send it to you somehow.

 

[Harvey12]

Hi Michael,

What a fantastic response! That was some remarkable work, thank you very much, I have attempted it myself and it's nearly there, I am around halfway through and all looking good so far, honestly, thank you so much!

You mentioned the possibility of sending me your chart, if you could that would be so much appreciated?

My Gmail is: harvey250396@gmail.com I should think that is the easiest way to send the chart?

Many thanks again,
Harvey

 

[Michael_E]

I've sent it, the second sheet is the actual chart, the first sheet is for data entry.