Monthly Timeline to show active vs down time

[KelMac]

Hello all of you wonderfully brilliant Excel experts!! I hope you can help...

I need to create a chart/graph (whatever will do what I need, fairly easily) to show the active & down times for multiple companies.

I tried using a bar chart, but I'm not able to tell Excel how I want the information formatted...
Essentially what I would like to see is the companies on the left (with a different colored bar for each company), along the bottom of the chart I would like the months (Jan '17, Feb '17, etc.). So that said company's colored bar would only show along the months the project is scoped for (see columns 1st Del & Last Del).

Here's the tricky part - the only indication of which company is handling which project is by an entry of the 'budget' or a '0' to show they need to supply a budget. All blanks fields are to be disregarded (as they are not participating in that project).

I really hope I have explained this thoroughly, but if not, I will be happy to elaborate to any wonderful individual who chooses to help me!!!

Thank you in advance!

1st Del	Last Del	#Units	SqFt	Plan	ABC Co.	DEF Co.	GHI Co.	JKL Co.	MNO Co.
02/20/17	12/31/17	40
02/20/17	12/31/17	285	150,000
07/15/17	12/31/17	80				$ -
02/01/17	11/31/17	50	72,000		$ 700,000			$ -
08/01/17	11/31/17	46	82,287	N	$ 800,000
05/29/17	12/31/17	60	70,000	N	$ 60,000			$ -
04/01/17	12/31/17	154						$ -
05/26/17	12/31/17	95		N		$ -
02/13/17	12/31/17	1		N	$ -
03/06/17	12/31/17	80			$ -
02/06/17	12/31/17	87	41,788	Y	$ -
01/23/17	12/31/17	361	407,000	Y		$ 3,886,388		$ -
07/01/17	12/31/17							$ -
03/01/17	12/31/17		15,454			$ -		$ -
03/01/17	03/12/18	80	48,375
03/20/17	03/05/18	78	132,600			$ -		$ -
08/01/17	10/1017	137		Y					$ 1,000,000

<tbody>
</tbody>

 

[dUBBINS]

You say the companies are on the left. But are they really on the top (e.g. ABC Co.)? And how are the projects identified? (if by Sq.Ft., then what if two had the same footage?).

 

[KelMac]

Yes, the company names are listed on the top of the spreadsheet, but on the timeline chart/graph I'd like them to be on the left.

#Units, Sqft & plan are columns that do not need to be used in the timeline chart/graph. (I left them in the example in case column location could cause issues when moving to the timeline chart/graph.)

 

[dUBBINS]

Ah. I see now how I misread your post. Okay, so each line is one project? They are not combined in any way?

Maybe someone else knows a clever trick to do that directly. Are you opposed to having some hidden columns sorting out the data; and then the graph pulling from those cells? I am not sure how to make a graph sort out what you are asking. But we could add a few formulas to build a table that is only for the graph and remains unseen; I am half way thinking the columns for the graph cannot be hidden, but we could place them well off screen at the very least.

Interested in that sort of a solution?

 

[dUBBINS]

Okay, actually I cannot even get that to work for what you are wanting. If it were me I would probably build some sort of custom spreadsheet that acted like a graph. However, I am far from an expert and there are probably more eloquent ways to solve this. And not knowing exactly what your wants/uses/needs are it would be hard to build it in this manner. Good luck to you, hopefully someone comes along with some more knowledge for you.

If not, I do not mind trying to build you a custom graph from scratch, but I don't know how big of a rush you are in. I probably wouldn't be able to work on it until the weekend. But I love problem solving and I kind of have an idea of how to build a tool to create a visualization of your data.

 

[KelMac]

Yes you are correct, each line is a different project, and no, they are NOT combined in any way. The only combinations there could be is if more than one company is working on the project (i.e see the 4th row w/ 50 unit - ABC & JKL are working on it) in which I would need the timeline to show it's timespan for each company.

I am NOT opposed to having some far off columns (or hidden if possible, if not, no big deal) sorting out the data. That sounds great!!

Ah. I see now how I misread your post. Okay, so each line is one project? They are not combined in any way?

Maybe someone else knows a clever trick to do that directly. Are you opposed to having some hidden columns sorting out the data; and then the graph pulling from those cells? I am not sure how to make a graph sort out what you are asking. But we could add a few formulas to build a table that is only for the graph and remains unseen; I am half way thinking the columns for the graph cannot be hidden, but we could place them well off screen at the very least.

Interested in that sort of a solution?

 

[dUBBINS]

I cannot figure out how Excel will allow this. I did find some free downloads online for Excel Timeline Templates. I didn't download and try them, but I feel this is likely your best route to accomplish what you need. I would personally have to build you an Excel tool, which is not a big deal. However, you shouldn't download anything from me since I am a stranger. Google "Excel timeline template" and find a trusted source to get yourself a premade template.

 

[Eric W]

Given this layout:

	A	B	C	D	E	F	G	H	I	J
1	1st Del	Last Del	#Units	SqFt	Plan	ABC Co.	DEF Co.	GHI Co.	JKL Co.	MNO Co.
2	2/20/2017	12/31/2017	40
3	2/20/2017	12/31/2017	285	150,000
4	7/15/2017	12/31/2017	80				$ -
5	2/1/2017	11/31/17	50	72,000		$700,000			$ -
6	8/1/2017	11/31/17	46	82,287	N	$800,000
7	5/29/2017	12/31/2017	60	70,000	N	$60,000			$ -
8	4/1/2017	12/31/2017	154						$ -
9	5/26/2017	12/31/2017	95		N		$ -
10	2/13/2017	12/31/2017	1		N	$ -		0
11	3/6/2017	12/31/2017	80			$ -
12	2/6/2017	12/31/2017	87	41,788	Y	$ -
13	1/23/2017	12/31/2017	361	407,000	Y		$3,886,388		$ -
14	7/1/2017	12/31/2017							$ -
15	3/1/2017	12/31/2017		15,454			$ -		$ -
16	3/1/2017	3/12/2018	80	48,375
17	3/20/2017	3/5/2018	78	132,600			$ -		$ -
18	8/1/2017	10/1017	137		Y					$1,000,000

<tbody>
</tbody>

Sheet1​

I created another tab that looks like:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		January-2017	February-2017	March-2017	April-2017	May-2017	June-2017	July-2017	August-2017	September-2017	October-2017	November-2017	December-2017
2	ABC Co.	0	1	1	1	1	2	2	3	3	3	3	3
3	DEF Co.	0	1	1	1	1	1	1	1	1	1	1	1
4	GHI Co.	0	0	1	1	1	1	1	1	1	1	1	1
5	JKL Co.	0	0	0	0	0	0	0	0	0	0	0	0
6	MNO Co.	0	0	0	0	0	0	0	1	1	1	1	1

<tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula A2 =INDEX(Sheet1!$A$1:$Z$1,1,ROW()+4)&"" B2 =SUMPRODUCT(--(B$1>=Sheet1!$A$2:$A$18),--(B$1<=Sheet1!$B$2:$B$18),--ISNUMBER(OFFSET(Sheet1!$F$2:$F$18,0,MATCH($A2,Sheet1!$F$1:$J$1,0)-1))) <tbody> </tbody>

<tbody>
</tbody>

Put the 1st day of each month in B1:M1, and just format the date as mmmm-yyyy. Put the formula in A2, drag down. Put the B2 formula in, and drag across and down. The number in the cell represents how many projects are being worked on in that month. I used 3 Conditional Formatting rules to generate the colors. You need 1 rule per color, so I just made it so that they repeat. Depending on how many colors you want, you can add more rules. To add them: select the whole sheet, click Conditional Formatting > New Rule > Use a formula and enter:

=AND(ROW()>1,COLUMN()>1,A1>0,MOD(ROW(),3)=0)

click Format... and choose a fill color.

For other colors, repeat the process and change the 0 to 1 and 2 and use a different color. For more colors, change the 3 to 5, and the 0 will be 0, 1, 2, 3, or 4.

I played with charts for a bit, and nothing seems to match your requirements. I'm sure that something could be devised, but it would take some work. Regardless of the method, is this the type of output you're looking for?

 

[KelMac]

Given this layout:

	A	B	C	D	E	F	G	H	I	J
1	1st Del	Last Del	#Units	SqFt	Plan	ABC Co.	DEF Co.	GHI Co.	JKL Co.	MNO Co.
2	2/20/2017	12/31/2017	40
3	2/20/2017	12/31/2017	285	150,000
4	7/15/2017	12/31/2017	80				$ -
5	2/1/2017	11/31/17	50	72,000		$700,000			$ -
6	8/1/2017	11/31/17	46	82,287	N	$800,000
7	5/29/2017	12/31/2017	60	70,000	N	$60,000			$ -
8	4/1/2017	12/31/2017	154						$ -
9	5/26/2017	12/31/2017	95		N		$ -
10	2/13/2017	12/31/2017	1		N	$ -		0
11	3/6/2017	12/31/2017	80			$ -
12	2/6/2017	12/31/2017	87	41,788	Y	$ -
13	1/23/2017	12/31/2017	361	407,000	Y		$3,886,388		$ -
14	7/1/2017	12/31/2017							$ -
15	3/1/2017	12/31/2017		15,454			$ -		$ -
16	3/1/2017	3/12/2018	80	48,375
17	3/20/2017	3/5/2018	78	132,600			$ -		$ -
18	8/1/2017	10/1017	137		Y					$1,000,000

<tbody>
</tbody>

Sheet1​

I created another tab that looks like:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		January-2017	February-2017	March-2017	April-2017	May-2017	June-2017	July-2017	August-2017	September-2017	October-2017	November-2017	December-2017
2	ABC Co.	0	1	1	1	1	2	2	3	3	3	3	3
3	DEF Co.	0	1	1	1	1	1	1	1	1	1	1	1
4	GHI Co.	0	0	1	1	1	1	1	1	1	1	1	1
5	JKL Co.	0	0	0	0	0	0	0	0	0	0	0	0
6	MNO Co.	0	0	0	0	0	0	0	1	1	1	1	1

<tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula A2 =INDEX(Sheet1!$A$1:$Z$1,1,ROW()+4)&"" B2 =SUMPRODUCT(--(B$1>=Sheet1!$A$2:$A$18),--(B$1<=Sheet1!$B$2:$B$18),--ISNUMBER(OFFSET(Sheet1!$F$2:$F$18,0,MATCH($A2,Sheet1!$F$1:$J$1,0)-1))) <tbody> </tbody>

<tbody>
</tbody>

Put the 1st day of each month in B1:M1, and just format the date as mmmm-yyyy. Put the formula in A2, drag down. Put the B2 formula in, and drag across and down. The number in the cell represents how many projects are being worked on in that month. I used 3 Conditional Formatting rules to generate the colors. You need 1 rule per color, so I just made it so that they repeat. Depending on how many colors you want, you can add more rules. To add them: select the whole sheet, click Conditional Formatting > New Rule > Use a formula and enter:

=AND(ROW()>1,COLUMN()>1,A1>0,MOD(ROW(),3)=0)

click Format... and choose a fill color.

For other colors, repeat the process and change the 0 to 1 and 2 and use a different color. For more colors, change the 3 to 5, and the 0 will be 0, 1, 2, 3, or 4.

I played with charts for a bit, and nothing seems to match your requirements. I'm sure that something could be devised, but it would take some work. Regardless of the method, is this the type of output you're looking for?

Eric, thanks for the input. Unfortunately, I want to use the information already listed in my current spreadsheet. (I'm trying to prevent having 2 places to update information regarding these projects.)

Thanks though!