Timeline analysis

[DenDaffn]

Hi, fellow Excel designers,

I've been searching the net for the entire day now. I'm looking for an easily analysable timeline. For example, this is how my data looks like:

It shows each entire day and the events that happened on that day in accordance to a conveyor belt where products are made.
Having searched all day I found a lot of things that are close to what I am searching, but never the real deal.
I found out that a lot of people use a Gantt Chart. Gantt Charts are good charts for process information and when a process starts and stops.
Some people use a chart with error lines. There are great charts if you want to make some sort of history line to relation of important events that happened on some days.

But my Chart is none of those.
This is a self made example of how I would like my chart to look.

It is like a Gantt Chart, but with different colors instead of new lines.
I've tried almost everything now, but don't seem to get the results that I want.

Does someone know a good way to do this? I've seen conditional styling, but that's somewhat strange looking. This should be used in an professional environment, so it has to look good.

I'm looking forward to your replies.

-------

D.H.
Excel vba Progger and sheet designer

-------
ImageHosting.com offers Image Hosting for free!

 

[MickG]

Hi, What about a Useform with a series of text boxes side by side, each very narrow representing a number of minutes in the day, say 15 minutes.
Then by looping through your time sheet, searching for times and Event code each Text box could be coloured accordingly to represent all events/times during the shift.
Regards Mick

 

[DenDaffn]

Dear Mick,

That sounds good. It's a lot of work though, but it just might do the trick. I'll get cracking but I won't put this thread on "Solved" status. Some people might have other sollutions.

Cheers! D.H.

 

[wolverineb]

This is just a stacked horizontal bar chart, isn't it?

I think if you include all of your intervals (i.e. include the time the machine is running) and do a little formatting, it will create exactly what you want. Looked like it to me when I used your info.

 

[DenDaffn]

Dear WolverineB,

Yes, exactly a stacked bar chart. Its like a Gantt Chart, but then stacked. But I seem not to get anywhere. I get how to make a Gantt Chart but don't seem to be getting the stacked principle. And then some color editting and I should be home safe. So close yet so far!

I stacked some charts before, but those were normal bar charts. It seems to be trickier with horizontal bar charts.

Cheers, D.H.

 

[MickG]

Hi, You've probably got all this:-
http://<TABLE style="WIDTH: 48pt; B...font></font></TD></TR></TBODY></TABLE></p><p>
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>http://support.microsoft.com/kb/213929</TD></TR></TBODY></TABLE>
Regards Mick

 

[DenDaffn]

Hello again,

Nice link you gave me there. The result is a chart that can be seen and studied allright, although it still isn't exactly what I wanted, it's a chart with some usefullness.

The main problem with this chart is the lack of "working time" in the data entry. On the chart you can see what percentage of lost process time is what errorcode, but you can't see where the error persisted on a daily scale.
Also do i have a problem with the colors of the error codes. Every error code is a different series in the chart, supposed to every unique error code.

The scale problem can be overcome with the last collumn I recently made (between next). But I can't get the data into the chart the way I want.
The main problem here is the data is generated from external data as in a *.csv-file.
This means I can't place the last column inbetween the data I am using now.
Does does make any cense? Please tell me if it doesn't.

D.H.

----

ImageHosting.com offers Image Hosting for free!

 

[MickG]

Hi, Yes it looks good , but I think its getting the control to reflect your true needs that creates problem.
I, don't want to give you more problems but the folowing is something you might like to try when you have a spare moment.
This is obviouly , just an example :-

The times "From and "To are shown in columns "C & D".
Colour codes, relating to you "Meanings" are shown in Column "B".
If you create a Userform with a Command Button and 51 seperate Text boxes (With very narrow widths) placed side by side across the form. widths.
Each text box represents 10 minutes of time from 7:30 to 4:00.
If you run the code below it will colour the text boxes, if any 5 minute period in the "From" to the "To" time for each line, falls within a TextBox 10 Minute time zone.
Change Column "C" Range in code to suit.
Sorry about the Commas in the time references, they wanted to show as decimals.
You'll see better when you run the code.
This may be a complete non starter for you , but its nice to play.

[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Column(A)  [/B][/COLOR] [COLOR=royalblue][B]Column(B)  [/B][/COLOR] [COLOR=royalblue][B]Column(C) [/B][/COLOR][COLOR=royalblue][B]Column(D) [/B][/COLOR]
1.      Date        Color code  From       To        
2.      01/01/2010  6           07,37      07,40     
3.      01/01/2010  1           08,12      08,20     
4.      01/01/2010  3           09,32      09,41     
5.      01/01/2010  6           10,05      11,15     
6.      01/01/2010  6           11,20      11,00     
7.      01/01/2010  3           11,37      11,59     
8.      01/01/2010  4           13,40      13,06     
9.      01/01/2010  6           13,20      13,50     
10.     01/01/2010  1           14,00      14,05     
11.     01/01/2010  4           14,30      16,00

[COLOR=navy]Sub[/COLOR] MG18Feb56
[COLOR=navy]Dim[/COLOR] tim, TB [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] cols, Dn [COLOR=navy]As[/COLOR] Range, Hmm [COLOR=navy]As[/COLOR] Double
tim = Array("07:30", "07:40", "7:50", "08:00", "08:10", _
"08:20", "08:30", "08:40", "08:50", "09:00", "09:10", _
"09:20", "09:30", "09:40", "09:50", "10:00", "10:10", _
"10:20", "10:30", "10:40", "10:50", "11:00", "11:10", _
"11:20", "11:30", "11:40", "11:50", "12:00", "12:10", _
"12:20", "12:30", "12:40", "12:50", "13:00", "13:10", _
"13:20", "13:30", "13:40", "13:50", "14:00", "14:10", _
"14:20", "14:30", "14:40", "14:50", "15:00", "15:10", _
"15:20", "15:30", "15:40", "15:50", "16:00")
cols = Array(, RGB(0, 0, 51), , RGB(255, 0, 0), _
    RGB(0, 204, 51), RGB(0, 255, 204), RGB(255, 255, 0))
[COLOR=navy][/COLOR] 
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Range("C2:C11")
 
 [COLOR=navy]For[/COLOR] Hmm = Dn To Dn.Next [COLOR=navy]Step[/COLOR] 0.0034722
    
[COLOR=navy]    For[/COLOR] TB = 0 To 50
           [COLOR=navy]If[/COLOR] Format(Hmm, "hh:mm") >= tim(TB) And Format(Hmm, "hh:mm") < tim(TB + 1) [COLOR=navy]Then[/COLOR]
            Controls("Textbox" & TB + 1).BackColor = cols(Dn.Offset(, -1))
            [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] TB
[COLOR=navy]  Next[/COLOR] Hmm
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick