Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Charts - Gantt Chart with Current Date Line

Posted on 2014-12-27
5
Medium Priority
?
8,917 Views
Last Modified: 2014-12-28
I'm trying to create a Gantt chart in Excel that will show a vertical line indicating the current (today's) date.  I have most of the construction down, including "padding" columns, but need help resolving one final issue.

The "today line" is broken (hidden) when occurring before an event.  It's covered by the series that pads the events to their correct starting date.  I made the series white with no borders and also tried no fill, but it covers it anyway.  
Gantt chart - breaking "today line"
I know intuitively that the series that pads the dates will need to be further broken up, but darn if I can get my head around it.  Hopefully, an expert here can get me pointed in the right direction.   See the attached file.

With Thanks,
-Glenn
Gantt-Chart---Horizontal-Bars.xlsx
0
Comment
Question by:Glenn Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 40519933
Hello,

I recommend a different approach. You can use a XY chart on the secondary axis with a single data point and an error bar for the today line.

Steps:

Add a series called MyLine. Set its value to cell $I$1 (the Today cell). Select the new series and format it to be on the secondary axis. Change the series chart type to XY Scatter. Edit the series again and set its Y value to a 1 (typed straight into the box) and the X value to cell $I$1.

Format the secondary Y axis to be minimum 0 and maximum 1.

Select the data point and add a vertical error bar with the settings "Minus", "No Cap", "Fixed Value" = 1
Format the error bar to be a thick line.
Select the secondary Y axis and delete it.
Format the data point of the Scatter series to have no marker.

Remove the two series for the padding and the todaybar. You don't need them anymore.

See attached.

cheers, teylyn
Gantt-Chart---Horizontal-Bars.xlsx
2
 
LVL 50
ID: 40519950
Some explanation why your approach will not work:

screenshot
1. The bars will always be stacked in the order that they are listed in the data source dialog.
2. There is no data in the source table for the future events. You use a formula to leave the cells blank. Even if there were data, it would result in the black narrow bar stacked on top of the red bar. (see 1.)

To create the illusion of one uninterrupted line, you could split the Start Date series into two series and insert another series between the two. The values for this series would be blank if in the past. That would be a very complex setup with many helper columns and calculations. It's possible, but a bit intense to set up.

Attached please find a file where this approach is on Sheet2.

cheers, teylyn
Gantt-Chart---Horizontal-Bars--1-.xlsx
0
 
LVL 27

Author Closing Comment

by:Glenn Ray
ID: 40520251
Fantastic and ingenious combination of chart types to resolve this issue.
0
 
LVL 27

Author Comment

by:Glenn Ray
ID: 40520253
I had tried to add a scatter chart, but had not tried the error bar approach.  Also, after doing your steps manually, I see that the order of execution is very important.

Thanks for your great solution.

Regards,
-Glenn
0
 
LVL 50
ID: 40520261
Thanks for the grade and for the feedback.

I never use error bars for whatever they are meant to be used for. They can be a mean little addition to the charting trick box, though.

BTW, my first computing work was on a Pr1me super-mini. :-))  I wrote my thesis on a Pr1me system word processor and created all the analysis and charts on some charting software that ran on that "box".  I had a student job and was responsible for the daily tape backup, so I was in and out of the computer room and up close and personal with the hardware. The system was living in a converted classroom and was the size of 10 to 12 shoulder-height fridges in a row.

My major was English and music, but I converted to IT after that learning experience.  Never looked back.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question