SUMPRODUCT using Time between two values

[Finny]

Thanks in advance for any help you can provide me. I have a sheet
with the following layout of data (shortened here and changed for
obvious reasons)

Venue Date Event Start Event End
Act

Disney 4/13/10 7:00 PM 10:00 PM
Goofy
MSG 2/13/10 1:00 PM 3:00 PM
Basketball
Park 3/14/10 6:00 PM 8:00 PM
Harlem Globetrotters


So, what I have is a user input a Venue, Date, Time Value, and I would
need the Act returned...
For example,

Disney, 4/13/10, 8:25:13 PM, would output Goofy because 8:25:13 PM
falls between the start and end.

I can't seem to get the function to work the way I want it to when
using dates and values that fall between two times. Please help if
you can.

Thanks.

Conor

 

[Pete_UK]

Are your start and end times proper Excel times which are just
formatted to look like they do, or are they text values?

Pete

 

[Finny]

Are your start and end times proper Excel times which are just
formatted to look like they do, or are they textvalues?

Pete

Pete,

The start and end times are actual time values, not text.
Let me know if you need anything else. Thanks.

 

[David Heaton]

Thanks in advance for any help you can provide me.  I have a sheet
with the following layout of data (shortened here and changed for
obvious reasons)

Venue          Date            Event Start         Event End
Act

Disney       4/13/10           7:00 PM            10:00 PM
Goofy
MSG          2/13/10          1:00 PM            3:00 PM
Basketball
Park          3/14/10           6:00 PM             8:00 PM
Harlem Globetrotters

So, what I have is a user input a Venue, Date, Time Value, and I would
need the Act returned...
For example,

Disney, 4/13/10, 8:25:13 PM, would output Goofy because 8:25:13 PM
falls between the start and end.

I can't seem to get the function to work the way I want it to when
using dates and values that fall between two times.  Please help if
you can.

Thanks.

Conor

Conor, your post title confused me as SUMPRODUCT will not return a
text value, only the value of a cell.
However the sumproduct function will work with times

Assume your titles were in cells A1:E1 with the formula below

=SUMPRODUCT((A2:A4=A23)*(C2:C4=A24)*(D24<=A25)*(E2:E4>=A25),(B2:B4))

This will return 0



To get the act name, list the acts somewhere else on your sheet and
give them a number then put that number in the data grid instead of
the ACT name.

for the example i'll say that the ACT names are in Y1:Y10 and the
reference numbers in Z1:Z10


=INDEX(Y1:Y10,MATCH(SUMPRODUCT((A2:A4=A23)*(C2:C4=A24)*(D24<=A25)*(E2:E4>=A25),
(B2:B4)),Z1:Z10,0),1)


hth

Regards

David

 

[Finny]

Conor, your post title confused me as SUMPRODUCT will not return a
text value, only the value of a cell.
However the sumproduct function will work with times

Assume your titles were in cells A1:E1 with the formula below

=SUMPRODUCT((A2:A4=A23)*(C2:C4=A24)*(D24<=A25)*(E2:E4>=A25),(B2:B4))

This will return 0

To get the act name, list the acts somewhere else on your sheet and
give them a number then put that number in the data grid instead of
the ACT name.

for the  example i'll say that the ACT names are in Y1:Y10 and the
reference numbers in Z1:Z10

=INDEX(Y1:Y10,MATCH(SUMPRODUCT((A2:A4=A23)*(C2:C4=A24)*(D24<=A25)*(E2:E4>=A25),
(B2:B4)),Z1:Z10,0),1)

hth

Regards

David

Thanks Dave. I was able to rework your formula slightly to get it to
do what I needed.
Much appreciated.