Creating a timeline chart using time of day formatted cells

R

rmiller55

New Member
Joined
Jul 3, 2009
Messages
5
I understand that excel doesn't offer a floating bar chart, but this link provides a work around. Unforetunately I can't make it work with my start times in time of day format (14:09:44). I wish to see how often and for how long the calls overlap.

http://www.mrexcel.com/articles/timeline-chart-in-excel.php

Here is a sample of my spread sheet.

<TABLE style="WIDTH: 360pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=480 border=0 x:str><COLGROUP><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" width=200><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 150pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=200 height=17>time</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 112pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=149>call</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 98pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=131>duration in seconds</TD></TR><TR ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle="fontDataGridItemStyle"><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.590092592596">6/29/09 14:09</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 1</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>145</TD></TR></FORM><TR ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle="fontDataGridAlternatingItemStyle"><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.591840277775">6/29/09 14:12</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 2</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>46</TD></TR><TR ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle="fontDataGridItemStyle"><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.592430555553">6/29/09 14:13</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 3</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>245</TD></TR><TR ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle="fontDataGridAlternatingItemStyle"><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.593622685185">6/29/09 14:14</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 4</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>235</TD></TR><TR ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle="fontDataGridItemStyle"><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.593634259261">6/29/09 14:14</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 5</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>33</TD></TR><TR ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle="fontDataGridAlternatingItemStyle"><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.593900462962">6/29/09 14:15</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 6</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>38</TD></TR><TR class="" ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.598402777781">6/29/09 14:21</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 7</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>113</TD></TR><TR class="" ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.607303240744">6/29/09 14:34</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 8</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>47</TD></TR><TR class="" ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.608761574076">6/29/09 14:36</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 9</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>64</TD></TR><TR class="" ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.608784722222">6/29/09 14:36</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 10</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>51</TD></TR><TR class="" ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.609652777777">6/29/09 14:37</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 11</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>190</TD></TR><TR class="" ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.610162037039">6/29/09 14:38</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 12</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>53</TD></TR><TR class="" ***********="this.orgStyle=this.className; this.className=(this.className.indexOf('NoPadding')==-1) ? 'fontDataGridItemStyleHover' : 'fontDataGridNoPaddingItemStyleHover';" style="HEIGHT: 12.75pt" **********=this.className=this.orgStyle; height=17 orgStyle><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39993.614340277774"><FORM id=AjaxForm name=AjaxForm onsubmit="return __formHijack()" action=default.aspx?pg=80300&mn=80001&TimeRange=8&DateTime=6%2f29%2f2009+19%3a00&Duration=4&ID1=207&Type1=loc&Grp1=141&cd=true method=post>6/29/09 14:44</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">call 13</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>404</TD></TR></TBODY></TABLE>
The chart put both time and call along the Y axis, instead of plotting time as part of the bar graph for each call. My stacked chart isn't stacked since the only parameter on the bar is duration. I tried to copy and paste the chart, but it wouldn't. It appears to me the time format is a problem. I tried removing the date portion in the time column but got the same reult.

My PC is XP, my excel is 2003 Sp3.

Any suggestions would be great.


</FORM>
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Try converting your duration in seconds as follows: Place the following value into any unused cell =1/24/60/60 Then copy this cell, select your seconds data and paste special | multiply. When stacked with your start times (with the start times first, and the durations last), you should be able to adjust your x-axis minimum to, say, 39993.591. Then format the start time bars to "no fill" and "no line", and your durations will "float" right where they belong along the x-axis. If you can't see them at first, you may have overlooked the series order mentioned above. You may also want to consider setting the maximum value for x-axis at, say, 39993.621.
HTH - Larry
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
819
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
590
epactheactor
E
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
142
dilshod_k
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,788
Messages
6,121,582
Members
449,039
Latest member
Arbind kumar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top