Excel Graphing and Dates 2

[bjd4jc]

I have a list of projects with their respective start and end dates. I am trying to portray this into a graph that would look similar to this

         |
Proj A   |=====================
         |
         |
Proj B   |    =============================
         |
         |
Proj C   |       =======
         -------------------------------------
          09/01/2003             08/31/2003

Project A starts at 09/01/03 and ends at 05/31/03
Project B start at 09/15/03 and ends at 08/31/03
Project C Starts at 10/01/03 and ends at 10/31/03

I am having difficulty creating a chart like this. Any ideas or direction on how to do this?

 

[bjd4jc]

Correction of my previous post... all of the end dates years should be 2004 and not 2003

 

[bluedragon2]

http://office.microsoft.com/assista...ID=HA010346051033&CTT=6&Origin=EC010553071033

Blue

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

or:

http://download.com.com/3000-2064-10224160.html

Blue

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Hi,

1. On your data axis, the MINIMUM value will be the serial data value of the Earlist Date

2. You must have an OffsetDays column that is the difference between the MINIMUM and Start Date

3. You must have the SpanDays, Fin-Start

You plot OffsetDays and SpanDays.

OffsetDays series has no fill and no line

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

If you would rather have it on the grid itself, have a series of dates in a row (preferably in a vertical format to make the width of the cells smaller). Now say that your dates are in Col A and B, do a conditional format to change the color of the graph area cells to test if the date above the cell falls in that rows timeline. For example:

If you have 09/21/2002 in C1 and in A2 and B2 you have 09/21/2002 and 10/21/2002, for cell C2 do conditional formating that has formula is =AND(C1>=A2,C1<=B2) and chage the cell color if that case is true.

I can send you an example of this if you like.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[bjd4jc]

Would you send an example? My email address is bjd4jc@yahoo.com

I am working on doing Microsofts example Gantt Chart. I am always up for learning multiple ways of doing things. Your help is appreciated!

Thanks

Brian

 

[bluedragon2]

Tried sending to that email address, but getting failed delivery.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[bjd4jc]

Try again... this is my account that I have everything sent to and only use as needed. I logged into it and the mailbox was full from junk mail. I cleared it all out so you should be able to send it...

Sorry...

Thanks

Brian

 

[SkipVought]

If you want to change the color of a bar on the gantt chart, you create other column(s) for SpanDays based on the condition(s).

So assuming, for purposes of argument, that you have 3 columns for SpanDays, 1 is OK, 2 is LATE and 3 is EARLY AND the SpanDay values are mutually exclusive (only ONE of the three an have a non zero value), THEN each of those series has it's own color fill/pattern/border.

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bjd4jc]

BlueDragon-

I got your example. It is exactly what I am looking to do. Just have one question. In your formula for SpanDays you have =End-Start. How can you use the column headers instead of the cell reference??

Skip-

I like your idea of adding the ok, late and early dates. I am going to try and modify the example I got from Blue to do this. We just started a new program at our company that tracks how well we do on our dates.

Thanks alot guys, here's a star for you both!

 

[bluedragon2]

Not sure what you are asking, I didn't think I had span days in my example



Blue

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

That was my example.

Look at the SECOND workbook I sent to you and look at menu item Insert/Name/Define

In the Define Window are formulas for each name that keep the data table DYNAMIC!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[cjjn]

Hi Skip,

I am looking for a similar solution, would you mind sharing an example with me as well?

Thanks
cjjn

 

[SkipVought]

cjjn

This was nearly 2 years ago.

Please start a new thread and explain what you are trying to do.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[xlbo]

Please note also that it is against site rules to take threads off into emails except as a last resort.

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question