Hello,
I've recently created a Gantt chart on excel using conditional formatting etc. I'd like to implement a function where I can input a start and end date (a project start and end date for example) and the Gantt chart only show dates within this range. Maybe by hiding other cells perhaps or shrinking other cells to 0 column width maybe? not sure if this is a formula question or a macro question but any help would be greatly appreciated.
Thanks
It would help if you attached a sample Excel workbook.
To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.
Hope this helps.
Pete
Hey, here's the sheet. Pretty crude I know but it's a working process. Thanks
My plan is to add a sizable amount of years, 5 for example then create a cell where you input the start/end date of a project and it just shows the dates within that range. Hope that makes sense.
With the weekend come and gone and no further response, I will offer this cautious thought. You are using an in cell charting technique using conditional formatting. I see two basic ways to do this:
1) Use VBA to "hide" the columns that are not between the selected dates. The heart of this procedure will be the .Hidden property of each column.
2a) Use formulas to start the date range in K20 at the selected date and end at the selected date. Something like =cell with start date in K20. L20 would be IF(K20<=cell with end date,K20+1,"end") [or empty string "" or whatever].
2b) This will make the conditional formatting more difficult as you will need to use formulas to identify holidays and such rather than hand entered values.
Which approach do you want to use?
Originally Posted by shg
Hello Mr Shorty,
I've figured out how to use VBA to alter the start date by hiding all columns before the date in a certain cell using this code...
I thought that to do the same for an end date I would just change the E20 to the cell I'm inputting the end date "I20" and change this "xCell.Value < Target.Value" to this "xCell.Value > Target.Value" and add it after this code to have something like this...
Please Login or Register to view this content.
I just can't get this second section of code to work. Any advice would be greatly appreciated.Please Login or Register to view this content.
Thanks
Last edited by LukeJB1994; 08-28-2018 at 05:39 AM.
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
The code tags have been added - thanks for that (I'm sure Ali will remove her post when she realises).
As there was no data in your file, it is hard to envisage how you would use this, as you have two start dates and durations - one for planned and one for actual. Do you intend to manually colour the appropriate date boxes when the data in the initial columns is added?
I would suggest another approach, which automatically fills in the date bars when data is added, and this can be seen in the attached file. It covers a year, but the year can be chosen from the drop-down in cell B1, so there is no need to have 5 years' worth of dates shown in the chart. Furthermore, the date in cell I5, from which all the other dates are derived, is calculated from the month of the earliest entry, so there is no need to hide columns. If you change year to 2019, the chart will still start from the month of the earliest entry, just in a different year. Of course, you could manually add the start date to I5 (make sure it is the 1st of a month), or have another drop-down to choose the starting month.
The bars on the chart are coloured using conditional formatting - 2 colours are used, and these are dependent on the values in column F (this was a planning chart for a conference organiser which I put together some time ago).
Maybe you can adapt this approach to your own requirements.
Hope this helps.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks