Chart or Graph data based on hours

[vullkunnraven]

Question:

I would like to chart (pie or graph) sales based on the hour they occured.

In excel, I have column A - the time stamp, and column B, the sale. Note that each sale has its own unique time stamp.

Any idea how to express this visually? I don't mind rounding down to the hour.

Thanks!!!


<table style="border-collapse: collapse; width: 131pt;" width="175" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 15pt;" height="20"><td style="height: 15pt; width: 83pt;" width="111" height="20">Time</td> <td style="width: 48pt;" width="64">Sale</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5:02:37PM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9:52:09AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5:10:15PM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">10:30:29AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">11:31:31AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4:40:41AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5:28:48AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">7:08:34AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5:04:07AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">11:50:01AM</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9:48:58PM</td> <td align="right">1</td> </tr> </tbody></table>
<table style="border-collapse: collapse; width: 131pt;" width="175" border="0" cellpadding="0" cellspacing="0"><col style="width: 83pt;" width="111"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 83pt;" width="111" height="20">
</td><td style="width: 48pt;" width="64">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt;" height="20">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt;" height="20">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt;" height="20">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt;" height="20">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt;" height="20">
</td><td align="right">
</td></tr></tbody></table>

 

[latinmaxima]

Ok now how do I post to the forum I have in installed and set as an add in cause this doesn't look quite right.


1st

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: ARIAL,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 77px"><COL style="WIDTH: 25px"><COL style="WIDTH: 71px"><COL style="WIDTH: 88px"><COL style="WIDTH: 25px"><COL style="WIDTH: 25px"><COL style="WIDTH: 123px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 98px"><COL style="WIDTH: 61px"><COL style="WIDTH: 61px"><COL style="WIDTH: 61px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD> </TD><TD> </TD><TD></TD><TD> </TD><TD></TD><TD></TD><TD></TD><TD> </TD><TD></TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial" colSpan=3>Calls Dropped This Hour</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">Hour</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">Called #</TD><TD style="TEXT-ALIGN: center">Call ANI</TD><TD> </TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">Call Skills</TD><TD> </TD><TD style="TEXT-ALIGN: center">Call Abandon Time*</TD><TD> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">Hour Start</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">XXX</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">Category Name</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial">XXX</TD><TD style="TEXT-ALIGN: center">7009</TD><TD style="TEXT-ALIGN: center">7018</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">7804171982</TD><TD>n/a</TD><TD></TD><TD></TD><TD></TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">0:15</TD><TD> </TD><TD style="TEXT-ALIGN: right">0:00</TD><TD style="TEXT-ALIGN: right">0:59</TD><TD> </TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">0:45</TD><TD style="TEXT-ALIGN: right">7018</TD><TD style="TEXT-ALIGN: right">7804060410</TD><TD>n/a</TD><TD></TD><TD> </TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD> </TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">1:59</TD><TD> </TD><TD> </TD><TD></TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">4033953300</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD> </TD><TD style="TEXT-ALIGN: right">2:00</TD><TD style="TEXT-ALIGN: right">2:59</TD><TD></TD><TD> </TD><TD> </TD><TD></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">1:34</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">9042592901</TD><TD>XXX</TD><TD> </TD><TD>XXX</TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">1:34</TD><TD> </TD><TD style="TEXT-ALIGN: right">3:00</TD><TD style="TEXT-ALIGN: right">3:59</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">1:35</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">9042592901</TD><TD>n/a</TD><TD> </TD><TD>XXX</TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">1:35</TD><TD>XXX</TD><TD style="TEXT-ALIGN: right">4:00</TD><TD style="TEXT-ALIGN: right">4:59</TD><TD> </TD><TD>XXX</TD><TD>XXX</TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">2:57</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD>n/a</TD><TD> </TD><TD>XXX</TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">2:57</TD><TD> </TD><TD style="TEXT-ALIGN: right">5:00</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD>XXX</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">7018</TD><TD style="TEXT-ALIGN: right">4038777270</TD><TD>n/a</TD><TD> </TD><TD>XXX</TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD> </TD><TD style="TEXT-ALIGN: right">6:00</TD><TD style="TEXT-ALIGN: right">6:59</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">3:10</TD><TD style="TEXT-ALIGN: right">7018</TD><TD style="TEXT-ALIGN: right">7805045463</TD><TD>XXX</TD><TD> </TD><TD> </TD><TD>XXX</TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">3:11</TD><TD> </TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">7:59</TD><TD> </TD><TD>XXX</TD><TD>XXX</TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">4:05</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">3862905503</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">4:05</TD><TD>XXX</TD><TD style="TEXT-ALIGN: right">8:00</TD><TD style="TEXT-ALIGN: right">8:59</TD><TD> </TD><TD> </TD><TD> </TD><TD>XXX</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">4:06</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">3862905503</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">4:06</TD><TD> </TD><TD style="TEXT-ALIGN: right">9:00</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD> </TD><TD>XXX</TD><TD>XXX</TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">9288480022</TD><TD>n/a</TD><TD> </TD><TD>XXX</TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD> </TD><TD style="TEXT-ALIGN: right">10:00</TD><TD style="TEXT-ALIGN: right">10:59</TD><TD>XXX</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">5:32</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">9288480022</TD><TD>XXX</TD><TD> </TD><TD> </TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">5:32</TD><TD>XXX</TD><TD style="TEXT-ALIGN: right">11:00</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD> </TD><TD>XXX</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">5:32</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">9288480022</TD><TD>XXX</TD><TD>XXX</TD><TD>XXX</TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD>XXX</TD><TD style="TEXT-ALIGN: right">12:00</TD><TD style="TEXT-ALIGN: right">12:59</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">5:40</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">9288480022</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD> </TD><TD>XXX</TD><TD style="TEXT-ALIGN: right">5:40</TD><TD> </TD><TD style="TEXT-ALIGN: right">13:00</TD><TD style="TEXT-ALIGN: right">13:59</TD><TD>XXX</TD><TD> </TD><TD> </TD><TD>XXX</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right">5:50</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD>XXX</TD><TD>XXX</TD><TD> </TD><TD>XXX</TD><TD>XXX</TD><TD style="TEXT-ALIGN: right">5:50</TD><TD> </TD><TD style="TEXT-ALIGN: right">14:00</TD><TD style="TEXT-ALIGN: right">14:59</TD><TD> </TD><TD> </TD><TD>XXX</TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right">5:54</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">9288480022</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD> </TD><TD>XXX</TD><TD style="TEXT-ALIGN: right">5:54</TD><TD> </TD><TD style="TEXT-ALIGN: right">15:00</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right">5:57</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">3044213396</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">5:57</TD><TD> </TD><TD style="TEXT-ALIGN: right">16:00</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD> </TD><TD>XXX</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right">5:57</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">3044213396</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">5:57</TD><TD>XXX</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">17:59</TD><TD>XXX</TD><TD> </TD><TD> </TD><TD>XXX</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: right">5:58</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">3044213396</TD><TD>n/a</TD><TD>XXX</TD><TD> </TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">5:58</TD><TD>XXX</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">18:59</TD><TD> </TD><TD>XXX</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: right">6:10</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">3044213396</TD><TD>XXX</TD><TD> </TD><TD> </TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">6:10</TD><TD> </TD><TD style="TEXT-ALIGN: right">19:00</TD><TD style="TEXT-ALIGN: right">19:59</TD><TD> </TD><TD> </TD><TD>XXX</TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: right">6:31</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">3044213396</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD>XXX</TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">6:31</TD><TD> </TD><TD style="TEXT-ALIGN: right">20:00</TD><TD style="TEXT-ALIGN: right">20:59</TD><TD>XXX</TD><TD> </TD><TD>XXX</TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">3044213396</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD>XXX</TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">6:43</TD><TD> </TD><TD style="TEXT-ALIGN: right">21:00</TD><TD style="TEXT-ALIGN: right">21:59</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: right">6:49</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">3162395017</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD> </TD><TD>n/a</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD> </TD><TD style="TEXT-ALIGN: right">22:00</TD><TD style="TEXT-ALIGN: right">XXX</TD><TD>XXX</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: right">7:23</TD><TD style="TEXT-ALIGN: right">7008</TD><TD style="TEXT-ALIGN: right">9807215254</TD><TD>n/a</TD><TD> </TD><TD> </TD><TD> </TD><TD>XXX</TD><TD style="TEXT-ALIGN: right">7:23</TD><TD> </TD><TD style="TEXT-ALIGN: right">23:00</TD><TD style="TEXT-ALIGN: right">23:59</TD><TD> </TD><TD> </TD><TD> </TD><TD>XXX</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>M3</TD><TD>=IF(N3>0,K3,"")</TD></TR><TR><TD>N3</TD><TD>=SUMPRODUCT(--($A$3:$A$2500>=K3),--($A$3:$A$2500<=$L3),--($B$3:$B$2500=N$2))</TD></TR><TR><TD>O3</TD><TD>=SUMPRODUCT(--($A$3:$A$2500>=L3),--($A$3:$A$2500<=$L3),--($B$3:$B$2500=O$2))</TD></TR><TR><TD>P3</TD><TD>=SUMPRODUCT(--($A$3:$A$2500>=M3),--($A$3:$A$2500<=$L3),--($B$3:$B$2500=P$2))</TD></TR><TR><TD>M4</TD><TD>=IF(N4>0,K4,"")</TD></TR><TR><TD>M5</TD><TD>=IF(N5>0,K5,"")</TD></TR><TR><TD>M6</TD><TD>=IF(N6>0,K6,"")</TD></TR><TR><TD>M7</TD><TD>=IF(N7>0,K7,"")</TD></TR><TR><TD>M8</TD><TD>=IF(N8>0,K8,"")</TD></TR><TR><TD>M9</TD><TD>=IF(N9>0,K9,"")</TD></TR><TR><TD>M10</TD><TD>=IF(N10>0,K10,"")</TD></TR><TR><TD>M11</TD><TD>=IF(N11>0,K11,"")</TD></TR><TR><TD>M12</TD><TD>=IF(N12>0,K12,"")</TD></TR><TR><TD>M13</TD><TD>=IF(N13>0,K13,"")</TD></TR><TR><TD>M14</TD><TD>=IF(N14>0,K14,"")</TD></TR><TR><TD>M15</TD><TD>=IF(N15>0,K15,"")</TD></TR><TR><TD>M16</TD><TD>=IF(N16>0,K16,"")</TD></TR><TR><TD>M17</TD><TD>=IF(N17>0,K17,"")</TD></TR><TR><TD>M18</TD><TD>=IF(N18>0,K18,"")</TD></TR><TR><TD>M19</TD><TD>=IF(N19>0,K19,"")</TD></TR><TR><TD>M20</TD><TD>=IF(N20>0,K20,"")</TD></TR><TR><TD>M21</TD><TD>=IF(N21>0,K21,"")</TD></TR><TR><TD>M22</TD><TD>=IF(N22>0,K22,"")</TD></TR><TR><TD>M23</TD><TD>=IF(N23>0,K23,"")</TD></TR><TR><TD>M24</TD><TD>=IF(N24>0,K24,"")</TD></TR><TR><TD>M25</TD><TD>=IF(N25>0,K25,"")</TD></TR><TR><TD>M26</TD><TD>=IF(N26>0,K26,"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[gardnertoo]

The formulas in cells O3 and P3 are referring to the wrong column. Change the formula in cell N3 from this:
=SUMPRODUCT(--($A$3:$A$2500>=K3),--($A$3:$A$2500<=$L3),--($B$3:$B$2500=N$2))
to this:
=SUMPRODUCT(--($A$3:$A$2500>=$K3),--($A$3:$A$2500<=$L3),--($B$3:$B$2500=N$2))
then copy it across.

 

[latinmaxima]

I made the correction you displayed yet Column O still diplays "0" and it should display two.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					XXX									Calls Dropped This Hour
2	XXX	XXX	Call ANI		Call Routed CSQ	Agent Name	Call Skills		Call Abandon Time*		Hour Start	Hour End	Category Name	7008	7009	7018
3	XXX	7018	XXX	n/a				n/a	0:15		XXX	0:59		0	0	0
4	0:45	7018	XXX	XXX		XXX		n/a	0:45	XXX	1:00	1:59
5	1:30	7018	4033953300	n/a				XXX	1:30	XXX	2:00	2:59	XXX
6	1:34	7008	9042592901	XXX	XXX			n/a	1:34		3:00	XXX	XXX
7	1:35	7008	9042592901	n/a		XXX	XXX	n/a	1:35	XXX	4:00	4:59				XXX
1st

 

[gardnertoo]

Sounds like maybe the hours in column A might be formatted as text instead of numbers. When I put text of 0:45 in that column instead of the number, the formula doesn't find it.

 

[latinmaxima]

Its formatted in time as 13:30 selection. Which time Format did you use? I have them set at 24 time if that makes a difference.

 

[gardnertoo]

There are two things combining to prevent the formula from working.

First, the extension numbers in column B are not numbers, but "numbers stored as text". When I copy one of those cells and paste it into the N2, O2, or P2 cells, the formulas could work, but...

Second, the times in column A include the date, whereas the times in columns K and L do not. Those times are interpreted by Excel as being on the fictional day of January 0, 1900. This means that none of the times on January 1 2010 will ever occur before a time on January 0, 1900, so none of them meet the conditions in the SUMPRODUCT formula.

The fix: in the N2, O2, and P2 cells, instead of typing the number 7008, type the formula ="7008" This will create a text version of the number instead of a numerical version of the number.
Then in cell K3 enter the formula =INT(A3) The result of this formula is midnight of the morning of the date on which the calls got dropped.
Cell K4 gets the formula =K3+1/24 and copy that down to cell K26

A new formula for cell N3: =SUMPRODUCT(--($A$3:$A$2500>=$K3),--($A$3:$A$2500<$K3+1/24),--($B$3:$B$2500=N$2))

Now that column L is being ignored, once everything works you may either delete those formulas, or delete the whole column.

Hopefully that gets it done.

 

[latinmaxima]

That did it and I was able to copy that to all the individual date tabs. Thank you so much.