Return excact data if between 2 time stamps

[troublesum08]

Hi. I'm not sure of what formula will get the desired results, but here is what I'm trying to accomplish. I have a data table where column A lists "names", column B lists "items purchased" and column C lists "time purchased". I am trying to use a formula to show all information where items were purchased between the times 17:00:00 and 23:00:00. Can I use nested IF statements or some type of SUMPRODUCT formula? I have a non-changing list of names that can be used as a Vlookup reference if that works better. I am lost, any help is greatly appreciated.

This is my first post so have mercy on the newbie.

troublesum08

 

[markmzz]

Sorry, here is an example since my image didn't work out right. I'm looking for results in table 2.

Table1...
NAME ITEM TIME
John pen 19:47
Mike paper 01:30
Tom ink 23:20

Table2...(Transaction results between 17:00 and 23:00)
NAME ITEM TIME
John pen 19:47

Troublesum08,

Try the following.

Assuming your data is in the range A4:C23 in the worksheet Plan1, type the following in the cells indicated:

C1 - CalcField
C2 - = AND(C5>=TIME(17,0,0),C5<=TIME(23,0,0))

Then do the following (for Excel 2003):

On the Data menu, point to Filter, and select Advanced Filter.

Then, type the following (in the indicated fields on the dialog box Advanced Filter):

List range: Plan1!A4:C23
Criteria range: Plan1!C1:C2

Finally, click OK.

Markmzz

 

[T. Valko]

My number format results show as false when testing with the (IsNumber) formula.

OK, we need to convert those TEXT time entries into numeric time entries.

Sometimes this will work...

Select the range of times.
Goto the menu Data>Text to Columns
Just click Finish

Check to see if that converted the times to numeric times:

=ISNUMBER(C3)

 

[markmzz]

Troublesum08,

Another solution.

Assuming your data is in the range A4:C23 in the worksheet Plan1, do the following (for Excel 2003):

Select cell A3 and, on the Data menu, point to Filter, and select AutoFilter.

Now, open the menu of the Time field (cell C3) and click Customize.

In the Custom Autofilter dialog box, do the following:

In the 1st box choice is greater than or equal to and in the box beside type 17:00:00

In the 2nd box choose is less than or equal to and in the box beside type 23:00:00

Leave marked the operated And.

Finally, click OK.

Markmzz

 

[T. Valko]

Troublesum08,

Another solution.

Assuming your data is in the range A4:C23 in the worksheet Plan1, do the following (for Excel 2003):

Select cell A3 and, on the Data menu, point to Filter, and select AutoFilter.

Now, open the menu of the Time field (cell C3) and click Customize.

In the Custom Autofilter dialog box, do the following:

In the 1st box choice is greater than or equal to and in the box beside type 17:00:00

In the 2nd box choose is less than or equal to and in the box beside type 23:00:00

Leave marked the operated And.

Finally, click OK.

Markmzz

If you've been reading the replies then you should know that the OP has already tried that!

 

[troublesum08]

Is there a formula that can give me the same results? It is easier for me to manipulate the data without using the filter. The filter option would be a worst case scenario solution for me. I'm trying to make it as automated as possible for users who have even less knowledge of excel than I do.

Thanks Again

 

[T. Valko]

Is there a formula that can give me the same results? It is easier for me to manipulate the data without using the filter. The filter option would be a worst case scenario solution for me. I'm trying to make it as automated as possible for users who have even less knowledge of excel than I do.

Thanks Again

With your data...

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Names</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Items</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Time</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name1</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item1</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5:44 AM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1:52 AM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">11:55 PM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">4:03 PM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">7:47 PM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1:19 AM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">6:14 AM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">8:20 AM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1:11 PM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">4:48 AM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">10:08 AM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name12</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item12</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3:56 PM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name13</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item13</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">7:52 AM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name14</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item14</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3:16 AM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">6:22 AM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name16</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item16</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1:16 PM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name17</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item17</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5:08 PM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name18</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item18</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">6:06 PM</TD></TR></TBODY></TABLE>


And this is the summary area...

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Start</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">End</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Count</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5:00 PM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">11:00 PM</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">7:47 PM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name17</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item17</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5:08 PM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">name18</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">item18</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">6:06 PM</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR></TBODY></TABLE>


Enter this formula in G2. This will return the count of records that fall within the time range.

=COUNTIF(C2:C19,">="&E2)-COUNTIF(C2:C19,">"&F2)

Enter this array formula** in E4. This will extract the data that falls within the time range.

=IF(ROWS(E$4:E4)>$G$2,"",INDEX(A$2:A$19,SMALL(IF($C$2:$C$19>=$E$2,IF($C$2:$C$19<=$F$2,ROW(A$2:A$19))),ROWS(E$4:E4))-ROW(A$2)+1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy across to G4 then down until you get a row of blanks.

Format G4:Gn as Time

 

[troublesum08]

T. Valko,
That last suggestion along with the "text to columns" tip did the trick. I've never used an INDEX before, so this is something I can play with and expand on in the future. THANK YOU!!

Mark-TY for your time and replies also. I learned a trick or 2 about filters thanks to your input!

troublesum08

 

[T. Valko]

T. Valko,
That last suggestion along with the "text to columns" tip did the trick. I've never used an INDEX before, so this is something I can play with and expand on in the future. THANK YOU!!

Mark-TY for your time and replies also. I learned a trick or 2 about filters thanks to your input!

troublesum08

Good deal. Thanks for the feedback!

 

[markmzz]

T. Valko,
That last suggestion along with the "text to columns" tip did the trick. I've never used an INDEX before, so this is something I can play with and expand on in the future. THANK YOU!!

Mark-TY for your time and replies also. I learned a trick or 2 about filters thanks to your input!

troublesum08

Troublesum08,

Thanks for the feedback.

And consider using, when possible, the powerful Advanced Filter feature in Excel.

Markmzz