Match if between 2 values (i.e. multiple criteria)

[Teapotlid]

I have this input table tracking time spent on various activities over a year (first table below). I want to show it in an output table with rows as days and time slots as columns (see 2nd table below) to get an overview of how time was spent.
I can use IndexMatchMatch in I2 for the Aug 3 type activity as the times neatly coincide with the output table.
For the Aug 4 activities I have a problem as I want to match if the time in output table row 1 is equal to/between the times in the input table columns B & C.
I've tried loads of different options & am stumped.
Surely this should be doable?
Would really appreciate help from the experienced index/matchers here.
Thanks in advance.

Input Table (c 1 yr's worth of data - not always in order)

	A	B	C	D
1	Date	From	To	Activity
2	Aug 3, 2017	12:00 PM	1:00 PM	Meetings
3	Aug 4, 2017	1:00 PM	5:00 PM	Thinking
4	Aug 4, 2017	5:00 PM	8:00 PM	Meetings

<tbody>
</tbody>

Desired Output Table (I'll use conditional formatting to show activities by color)

	H	I	J	K	L	M	N	O	P	Q	R
1		12:00 PM	1:00 PM	2:00 PM	3:00 PM	4:00 PM	5:00 PM	6:00 PM	7:00 PM	8:00 PM	9:00 PM
2	Aug 3, 2017	Meetings
3	Aug 4, 2017		Thinking	Thinking	Thinking	Thinking	Meetings	Meetings	Meetings

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 218px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Aug 3, 2017

<tbody>
</tbody>

 

[inactiveuserps07]

@Teapotlid, given your example above, place the following formula into I2:

=IFERROR(INDEX($D$2:$D$4,SUMPRODUCT(($A$2:$A$4 = $H2)*($B$2:$B$4 <= I$1)*($C$2:$C$4 > I$1)*(ROW($D$2:$D$4)))-1),"")

Select cell I2 and drag-copy the formula over to R2. Then, with I2:R2 still selected, drag-copy down to fill new rows as required.

 

[Teapotlid]

Thanks for this - I was clearly stuck on Match as an answer!

I've been trying it out with more inputs and a bigger time range. My inputs start on 1st July & I've tested out one hour band per day. I'm finding places where it doesn't work.
- 3am-4am appears in both the 3am output column and the 4am column. Same problem with 6am-7am
- 4am-5am, 7am-8am, and 11pm-12am don't show anything.
- The date seems to have no impact on whether it works.

Any ideas?

 

[inactiveuserps07]

@Teapotlid, being a formula, there's no reason that one time should work over another. I would guess that the issue is that adjustments to the formula were made incorrectly or that you've changed the ranges involved. The only way for me to know would be to see what you see. If you can post the entirety of both existing ranges with their column and row headers, I'll take a look.

 

[Teapotlid]

@ErikTyler I guess I've just demonstrated that I don't understand how the formula works: I can't see why it'd work sporadically because I don't get how it's working. Being dim
Not sure how to attach here: via a Dropbox link?

 

[inactiveuserps07]

Dropbox link will work. Many people who offer solutions here are not allowed to access external links due to work restrictions; but since I work from home, I sometimes will.

Busy day today. I'll check back this evening, though.

 

[Teapotlid]

Many thanks. Really appreciate the support
https://www.dropbox.com/s/96faobg7w20kof8/TimeTracker.xlsx?dl=0