Chart Timeline Help

[antrawson]

Howdy, I have a load of data in an excel table that populates a timeline chart. I have a lot of repeat tasks that currently sit on loads of lines. How can I get it so its sat on one? I cant quite work it out
Thanks

Project.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	PROJECT TIMELINE
2								{42}
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30		Project Start	01/05/23			columns used to create the chart
31	CATEGORY	TASK	START	END	COLOR	Start	Blue	Red	Green	Brown	Orange	Purple
33	Key Dates	Holiday	01/06/23	01/06/23	Blue	01/06/23	1	0	0	0	0	0
34		Holiday	02/07/23	04/07/23	Blue	02/07/23	3	0	0	0	0	0
35		Holiday	12/07/23	13/07/23	Blue	12/07/23	2	0	0	0	0	0
36		Holiday	01/08/23	04/08/23	Blue	01/08/23	4	0	0	0	0	0
37						00/01/00	0	0	0	0	0	0
38						00/01/00	0	0	0	0	0	0
39						00/01/00	0	0	0	0	0	0
40						00/01/00	0	0	0	0	0	0
41						00/01/00	0	0	0	0	0	0
42						00/01/00	0	0	0	0	0	0
43						00/01/00	0	0	0	0	0	0
44						00/01/00	0	0	0	0	0	0
45						00/01/00	0	0	0	0	0	0
46						00/01/00	0	0	0	0	0	0
47						00/01/00	0	0	0	0	0	0
48						00/01/00	0	0	0	0	0	0
49						00/01/00	0	0	0	0	0	0
50						00/01/00	0	0	0	0	0	0
51						00/01/00	0	0	0	0	0	0
52						00/01/00	0	0	0	0	0	0
53						00/01/00	0	0	0	0	0	0
54			Insert new rows above this one
ProjectTimeline

Cell Formulas
Range	Formula
F33:F53	F33	=IF(ISBLANK(C33),0,C33)
G33:L53	G33	=IF(ISBLANK($D33),0,IF($E33=G$31,$D33-$C33+1,0))

Cells with Data Validation
Cell	Allow	Criteria
E32:E53	List	=$G$31:$L$31

 

[awoohaw]

i'm confused with what you are trying to streamline. It seems your calculations are all set and you don't have much more to do except copy/drag the formulas when needed?

 

[awoohaw]

Oh, I see, i just looked at the mini sheet. Your image has the chart at the top... which did not come in the minisheet.
Can you explain how you are building the chart?

 

[tj4242]

I have seen Gantt charting in Excel before and all of them used conditional formatting to draw the bars. I have an example below. But you will have to calculate it using VBA based on the project start date and format using something like this

    Dim bar As FormatCondition

    'Draw the blue bars
    Set bar = Range(ganttarea).FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(B3>=$BL5,B3<=$BM5)")
    With bar
        .Interior.Color = RGB(0, 0, 255)
    End With

Anyway, the example without VBA uses and =AND expressing to see if the date in row 3 is between BL and BM.

timeline chart(3401).xlsx
	A	B	C	D	E	F	G	H	I	J	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	BJ	BK	BL	BM
1		PROJECT TIMELINE
2		June 1, 2023																														July 1, 2023
3		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
4	Holiday 1																																																														Task	Start	End
5	Holiday 2																																																														Holiday 1	6/3/2023	6/21/2023
6	Holiday 3																																																														Holiday 2	7/3/2023	7/5/2023
7	Holiday 4																																																														Holiday 3	7/9/2023	7/9/2023
8																																																															Holiday 4	7/10/2023	7/15/2023
Sheet1

Cell Formulas
Range	Formula
A4:A7	A4	=BK5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:BJ7	Expression	=AND(B3>=$BL$8, B3<=$BM$8)	text	NO
B6:BJ6	Expression	=AND(B3>=$BL$7, B3<=$BM$7)	text	NO
B5:BJ5	Expression	=AND(B3>=$BL$6, B3<=$BM$6)	text	NO
B4:BJ4	Expression	=AND(B3>=$BL5,B3<=$BM5)	text	NO

 

[antrawson]

Apologies, I dont think I have explained all too well. I am basically wanting my input from the information that I put in to display on one line as opposed to one line for each. So basically like the top example in the photo attached