gant tool

[_ratS]

Hi, i am working on this gant tool and i would like to know if this is posible:
I write time information for a processes inside some columns:
ex. from 6am to 6:05am

and next i draw chart inside rows to make it look visualy better.

My question is: Could i be inserting time information and excel would then automatically draw the chart?

PS: the operations are repeteable, so it can be "from 6 to 6:05" and sometime later from "7 to 7:20". So the gant chart should be drawn twice in the same row or more.

I am including picture.

Ty all!

 

[_ratS]

hello? anyone?

 

[moonfish]

This entire gannt chart could be made with conditional formatting:

Excel Workbook
	A	B	C	D	E	F	G	H	I
2	machine1			machine2			machine3
3	begin	end	time	begin	end	time	begin	end	time
4	6:09	6:18	0:09	6:00	6:05	0:05	6:05	6:09	0:04
5	6:20	6:46	0:26	*	*	0:00	*	*	0:00
6	*	*	0:00	*	*	0:00	*	*	0:00
7	*	*	0:00	*	*	0:00	*	*	0:00
8	*	*	0:00	*	*	0:00	*	*	0:00
9	*	*	0:00	*	*	0:00	*	*	0:00
10	*	*	0:00	*	*	0:00	*	*	0:00
11	*	*	0:00	*	*	0:00	*	*	0:00
12	*	*	0:00	*	*	0:00	*	*	0:00
13	*	*	0:00	*	*	0:00	*	*	0:00
14	*	*	0:00	*	*	0:00	*	*	0:00
15	*	*	0:00	*	*	0:00	*	*	0:00
16	*	*	0:00	*	*	0:00	*	*	0:00
17	*	*	0:00	*	*	0:00	*	*	0:00
18	*	*	0:00	*	*	0:00	*	*	0:00
19	*	*	0:00	*	*	0:00	*	*	0:00
Sheet1

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
1	*	6:00										6:10										6:20										6:30										6:40
2	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
3	machine1	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
4	machine2	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
5	machine3	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet1

If you copy the array formulas across the rows, you'll get what you need. Just make sure that the second row (L2:BI2 in this example) contains the time for that column. So L2=6:00:00, M2=6:01:00, etc

 

[moonfish]

Oh and then apply conditional formatting to the gannt chart. If the value is 1, color it.

 

[_ratS]

OMG yeah thats exactly what i want, but i dont fully understand what should i do to make it work

could you take your time and write more step-by-step begginer article kinda text for me, cause right now i am so confused :S

btw i am working in excel 2003 is it ok?

 

[moonfish]

Step one:
Re-organize your time line. Add a row to your chart. In the first cell of this new row, set the time 6:00:00. The next, 6:01:00. Select these two cells and drag the contents all the way to the end of your gannt chart (to the right).
Dragging is done by moving the mouse to the bottom right corner of your selection, holding down the mouse button and, well, drag it

Step two:
In the first machine row, in the slot for the first minute, type the following formula:
=SUMPRODUCT(IF(L$2>=$A$4:$A$19,1,0),IF(L$2<$B$4:$B$19,1,0))
You have to replace the cell values with the cells that actually contain the data in your spreadsheet. L$2 refers to the row added in step one. $A$4:$A$19 refers to the start time of the processes. $B$4:$B$19 refers to the end time of the processes.
Once you've replaced these values, hold down CTRL and SHIFT and press ENTER. If you don't do this, the outcome of the formula will be a #VALUE error.

Step three:
Do the exact same thing for all the other machines in your system. Now select all the cells that contain these formulas (they should all be directly above each other) and drag these formulas across the entire row.

Step four:
Select all the cells in your gannt chart. Now go to the Excel menu: Format > Conditional Formatting. In the first drop-down box on the left, choose the 'Cell Value Is' option. In the second, 'larger than' and in the last, 0. Use the format button below to specify a format. I'd advise a fill in a single color.
You can also use different colors for different machines but then you'll have to apply the conditional formatting one row at a time.


Let me know if you get stuck. I might not respond immediately but I'll try to be as quick as I can.

 

[_ratS]

im getting an error when entering the "sumproduct" function and when pressing ctrl+shift+enter nothing happens

 

[moonfish]

Select the field with the sumproduct in it. Press F2. Press ctrl+shift+enter.

 

[_ratS]

still getting error

and when press ctrl shift enter it comes up

 

[moonfish]

This is because you skipped step 1.
Well, technically it is because the cell is referring to itself.

Put the formula in L3, not in L2.