how do you *chart* dates?

  • Thread starter Thread starter Smitty
  • Start date Start date
S

Smitty

Thanks in advance for anyone's help. (Office 2002) I've
been handed a "challenging" task. My leader wants to see
if we've made our target dates across all groups. The
data would look like this:

--------------------GROUP ONE-----------------------
Item: Item 1 Item 2 Item 3
Target Date: 5/25/04 4/20/04 5/7/04
Actual Date: 6/3/04 4/19/04 5/7/04

--------------------GROUP TWO-----------------------
etc.

Up under Group one
Item 1 was 9 days behind schedule
Item 2 was 1 day ahead of schedule
Item 3 was right on schedule

I was hoping to show a calendar of sorts for the Y-axis,
and show where our targets were, and where we passed or
failed. I really don't know. I don't even know how to
transfer dates into a graph. Any help at all would be
MUCH appreciated!!
 
I took your original data and made a line chart. Excel showed two
series, one for target, and one for actual. The Y axis automatically
showed dates, and the X axis showed Item 1, etc.

Then I thought it might be clearer to show whether the items finished
early or late. With the data in A1:D3, I added some formulas:

Cell B4
=IF(B3<B2,-(B2-B3),0)

Cell B5
=IF(B3<B2,0,B3-B2)

I filled the formulas across to fill columns C and D. Cell A4 says
"Early" and A5 says "Late". The sheet now looks like:

Item Item 1 Item 2 Item 3
Target 5/25/2004 4/20/2004 5/7/2004
Actual 6/3/2004 4/19/2004 5/7/2004
Early 0 -1 0
Late 9 0 0

I selected row 1 of this range, then held CTRL while selecting rows 4
and 5 (a discontiguous range was now selected), then made a stacked
column chart. The Late series rose from the horizontal axis, while the
Early series hung below it.

What you probably want, though, is a Gantt chart, which is commonly used
to display timelines for project management purposes. Excel doesn't
offer a Gantt chart template, but it's just a stacked bar chart. I have
a simple example and some links here:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343
http://peltiertech.com/Excel/Charts/GanttLinks.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks much! With your direction, I think I can figure
it out now!!!! (my boss sends his thanks too)
 
Back
Top