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]

Troublesum08,

Try the following array formula (you have to press CTRL+SHIFT+ENTER and not only ENTER after type the formula):

=SUM(IF(C2:C20<=TIME(23,0,0),IF(C2:C20>=TIME(17,0,0),B2:B20,0),0))<?XML:NAMESPACE PREFIX = TIME(23,0,0),IF(C2 /><TIME(23,0,0),IF(C2:C20><TIME(23,0,0),IF(C2:C20><TIME(23,0,0),IF(C2:C20><TIME(23,0,0),IF(C2:C20>

Markmzz
</TIME(23,0,0),IF(C2:C20>
</TIME(23,0,0),IF(C2:C20>
</TIME(23,0,0),IF(C2:C20>
</TIME(23,0,0),IF(C2:C20>

 

[T. Valko]

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

It sounds like you want to filter the data.

If this is your data:

Book1
	A	B	C
2	Names	Items	Time
3	name1	item1	5:44 AM
4	name2	item2	1:52 AM
5	name3	item3	11:55 PM
6	name4	item4	4:03 PM
7	name5	item5	7:47 PM
8	name6	item6	1:19 AM
9	name7	item7	6:14 AM
10	name8	item8	8:20 AM
11	name9	item9	1:11 PM
12	name10	item10	4:48 AM
13	name11	item11	10:08 AM
14	name12	item12	3:56 PM
15	name13	item13	7:52 AM
16	name14	item14	3:16 AM
17	name15	item15	6:22 AM
18	name16	item16	1:16 PM
19	name17	item17	5:08 PM
20	name18	item18	6:06 PM
Sheet1

And you want to see only the data with a time between 5:00 PM and 11:00 pm (inclusive)...

Book1
	A	B	C
2	Names	Items	Time
7	name5	item5	7:47 PM
19	name17	item17	5:08 PM
20	name18	item18	6:06 PM
Sheet1

The easiest way to do that is to use autofilter.

How to apply that depends on what version of Excel you're using.

 

[troublesum08]

Ty for the reply. I tried this formula and it doesn't return the information that occurred during the time frame. It doesn't address the names in column A at all.

 

[troublesum08]

I'm using excel 2003. When trying to auto filter the criteria as greater than or equal to 17:00 AND less than or equal to 23:00, no records appear...I know there should be something showing and the time format is the same.

TY for your assistance

troublesum08

 

[markmzz]

Ty for the reply. I tried this formula and it doesn't return the information that occurred during the time frame. It doesn't address the names in column A at all.

Troublesum08,

You press CTRL+SHIFT+ENTER and not only ENTER after type the formula
=SUM(IF(C2:C20<=TIME(23,0,0),IF(C2:C20>=TIME(17,0,0),B2:B20,0),0))<?XML:NAMESPACE PREFIX = TIME(23,0,0),IF(C2 /><TIME(23,0,0),IF(C2:C20><TIME(23,0,0),IF(C2:C20><TIME(23,0,0),IF(C2:C20><TIME(23,0,0),IF(C2:C20>?

Could you explain better what you want with an example?

Markmzz
</TIME(23,0,0),IF(C2:C20></TIME(23,0,0),IF(C2:C20></TIME(23,0,0),IF(C2:C20></TIME(23,0,0),IF(C2:C20>

 

[T. Valko]

I'm using excel 2003. When trying to auto filter the criteria as greater than or equal to 17:00 AND less than or equal to 23:00, no records appear...I know there should be something showing and the time format is the same.

TY for your assistance

troublesum08

Maybe your times are not true Excel times.

In Excel time is a numeric value that represents the fractional part of a day. It's simply formatted to look like a time entry.


For example...

• 12:00 AM = 0
• 8:00 AM = 0.33333333
• 12:00 PM = 0.50
• 6:00 PM = 0.750

Check and make sure your times are true Excel times. You can do that by testing that the time entry is a number.

If C3 contains a time entry:

=ISNUMBER(C3)

TRUE = a true time entry
FALSE = not a true time entry, probably a text string that looks like a time entry

 

[troublesum08]

My number format results show as false when testing with the (IsNumber) formula. Here is a better example of what I'm trying to do. I need to get the results in table 2.

Thanks Again

 

[Jon von der Heyden]

Hi troublesum08

Regarding your query to insert an image: You should either load your image to a photo sharing site and use the IMG tag along with the URL to the image on the photo sharing site, or you could try to show your table with the HTM maker.

 

[troublesum08]

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