Conditional Gantt chart

[novicehacker]

HI all
i'm trying to create a conditonal gantt chart (similar to this example: (http://www.mrexcel.com/tip058.shtml)). Referring to the example, there is no event that repeats more than once, in my case i'm creating the scheduled use of cars, therefore the events repeat, i.e a car is taken out say 8 for one hour and then again at 10, i can't figure out how to put this on a conditional Gantt chart. E.g following is a schedule
Time Out Time in
Car A 8:00 9:00
Car B 8:00 9:00
Car B 10:00 11:00
Car A 11:00 12:00
Car A 12:00 13:00

I would like to show the above info in a Gantt chart as:
8:00 9:00 10.00 11.00 12:00 13:00
CAR A USED USED
CAR B USED USED

basically my query is how can you create a conditional Gantt chart for repititive events, (in my case the usage of a specific Car). I'm guessing i'll have to write some kind of VBA code, not exactly my strongest forte!

Any answers would be appreciated and win you a drink if you happen to come down to Toronto.

thanks

 

[santeria]

Is a Gant Chart going to be what you want for this ??

Seems like the Gant chart will deal with the items you want on a time Basis, so the items seem irrelavent.

Do you have a working Gant Chart at the moment?

In what way exactly is it failing?

Can you show an example using the Colo Html maker?

Ta

 

[novicehacker]

car example.htm
	A	B	C	D	E	F	G	H	I	J
2				ThedataIhaverightnowshowsas:
3
4		START	END	8:00AM	9:00AM	10:00AM	11:00AM	12:00PM	1:00PM	2:00PM
5	CARA	8:00AM	9:00AM
6	CARA	10:00AM	11:00AM
7	CARB	9:00AM	12:00AM
8	CARB	1:00PM	2:00PM
9	CARA	12:00AM	1:00PM
10
11				Iwouldliketheabovetoshowas:
12				8:00AM	9:00AM	10:00AM	11:00AM	12:00PM	1:00PM	2:00PM
13			CarA
14			CarB
15
16
Sheet1

the above shows what i'd like to do, basically i'd like to show all the instances in which one car was used on the same row instead of on different rows, this is just a sample, in my actual example i have about 40 cars, who i'm tracking every day over the past year.....

 

[novicehacker]

just thought i'd refresh this topic if anyone can help me....i'm stumped.

 

[fairwinds]

Hi,

1. I changed your data a little bit as I don´t think your upper and lower charts says the same thing.

2. The formulas you see in the cells are not supposed to be there. Its the ones you are supposed to use in contitional formatting.

Book1
	A	B	C	D	E	F	G	H	I	J
1	ThedataIhaverightnowshowsas:
2
3		START	END	08:00	09:00	10:00	11:00	12:00	13:00	14:00
4	CARA	08:00	09:00	1	1	0	0	0	0	0
5	CARA	10:00	11:00	0	0	1	1	0	0	0
6	CARB	09:00	12:00	0	1	1	1	1	0	0
7	CARA	13:00	14:00	0	0	0	0	0	1	1
8	CARB	12:00	13:00	0	0	0	0	1	1	0
9
10				Iwouldliketheabovetoshowas:
11				08:00	09:00	10:00	11:00	12:00	13:00	14:00
12			CarA	1	1	1	1	0	1	1
13			CarB	0	1	1	1	2	1	0
14
Sheet1

 

[novicehacker]

thanks, i'm convinced there will be a fair degree of programming involved to do this, time i just don't have at the moment.

 

[fairwinds]

No No

Just one formula to put into conditional formatting for the new table.

=SUMPRODUCT(($A$4:$A$8=$C12)*(D$11 > =$B$4:$B$8)*($C$4:$C$8 > =D$11))

 

[novicehacker]

thanks, will check into what you did.