Monthly gridlines in Excel Gantt chart

[colabarris]

Hello,

I'm building an Excel-based tool that includes a Gantt chart. I've done it with a staked bar chart (see image below)

The issue I'm finding is that I need to have gridlines at the start of each month and I haven't found a way of doing it. I can adjust it manually, but the graph needs to automatically adjust if a new task is introduced and more gridlines are needed.

Any advice on how to do it?

Many thanks in advance!
Carlos

 

[Perpa]

Hello,

I'm building an Excel-based tool that includes a Gantt chart. I've done it with a staked bar chart (see image below)

The issue I'm finding is that I need to have gridlines at the start of each month and I haven't found a way of doing it. I can adjust it manually, but the graph needs to automatically adjust if a new task is introduced and more gridlines are needed.

Any advice on how to do it?

Many thanks in advance!
Carlos

Carlos,
Welcome to the Forum!
I am running Windows 7 and Excel 7 on a PC. What are you running?
What you asked can be accomplished if you are familiar with VBA.
As you already know about Gantt charts...
The range of dates can be adjusted by right clicking on the row of dates, then selecting 'Format Axis',
then changing the top number ('Minimum') to get the Start Date you want.

For some reason I couldn't open the image you attached.
But your table must include these items in adjacent columns: Task, Start Date, Duration, and End Date.

So, using a Gantt Chart I had developed earlier, I used the Macro Recorder and formatted the axis, and
this is the code I got:

Sub Test1()
    ActiveSheet.ChartObjects("Chart 8").Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 40909    ' 40909 is Excel time-date format for 1/1/2012 
    Range("K41").Select     'This is somewhere outside of the chart - change to fit with your chart
End Sub

You need to select your chart and look for the chart name in the little window below the 'Home' tab.
Then modify the above code for a variable date and to change the chart name.

To make it 'automatic' you need to make it run after all the data has been entered for that task...probably
after you have entered the End Date. Make a note of the columns where the Start Date and End Dates are entered.
Perpa

 

[colabarris]

Hello Perpa,

Thanks for your answer. Sorry, now I see that the image did not upload correctly for everyone. I've pasted it below again

I'm using VBA on my project so that is not an issue. Following your approach, I would be able to set correctly the gridline for the first month but then, since every month has a different number of days, the next monthly gridlines would not be accurate... Do you see my problem?

Best,
-Carlos

 

[colabarris]

I haven't been able to add the image, not sure why. But the main issue is explained on the last paragraph

 

[Perpa]

Hello Perpa,

Thanks for your answer. Sorry, now I see that the image did not upload correctly for everyone. I've pasted it below again

I'm using VBA on my project so that is not an issue. Following your approach, I would be able to set correctly the gridline for the first month but then, since every month has a different number of days, the next monthly gridlines would not be accurate... Do you see my problem?

Best,
-Carlos

Carlos,
This is when a picture is really worth a thousand words...
I am not clear on your statement "...I would be able to set correctly the gridline for the first month but then, since every
month has a different number of days, the next monthly gridlines would not be accurate." Normally, the Gantt chart
shows all the tasks on the same time line, and Excel knows how many days there are in any month.

I was able to load an image(picture) using the following...You might give it a try.
Perpa

Posting screenshots: (Copied from this Forum)

You can use Alt + Print Screen with the dialogue box open, paste into an image editor,
save and upload via image hosting site. I have used imgur.com.

Make sure the link you insert between the tags ends with the file type. .png .jpg etc
Make sure you copy the proper link from the image hosting site.

Perpa's comments:
First I used the Snipping Tool to get an image file saved; then I uploaded that image file to the filesharer , imgur.com...
I did not 'POST' to Imgur, I just uploaded it and copied the link provided. I edited the link description to just be 'Gantt
Chart Image' not an url add for imgur. com, which is the way it comes up if left unedited. Then I posted it to the thread
using the IMG framing you tried earlier.

 

[colabarris]

Imgur: The most awesome images on the Internet

 

[colabarris]

Hello Perpa,

Very sorry for the delay and thanks for your helping uploading the image

Hope you see what I mean. I can manually set the first gridline to July 1st (now it's June 25th), but then, how do I make the rest of the gridlines appear on August 1st, Sept 1st, etc?

Thank you!
-carlos

 

[Perpa]

Hello Perpa,

Very sorry for the delay and thanks for your helping uploading the image

Hope you see what I mean. I can manually set the first gridline to July 1st (now it's June 25th), but then, how do I make the rest of the gridlines appear on August 1st, Sept 1st, etc?

Thank you!
-carlos

carlos,
Click on the dates at the bottom of your chart...all the dates will be selected.
Then right click the dates again and the 'Format Axis' dialog box opens.

The first option under 'Axis Options' is labled 'Minimum'. Select 'Fixed' and enter the 5-digit
Excel date that you want your chart to begin with...'42370' is 1/1/2016.
When you select 'Close' your chart will be updated.
Perpa