Date within date range query

[Microsoft]

Hi All,

Sheet1

Start	End	Billing Period
01/01/1900	15/05/2017	1
16/05/2017	15/06/2017	2
16/06/2017	14/07/2017	3
15/07/2017	15/08/2017	4
16/08/2017	15/09/2017	5

<tbody>
</tbody>

Sheet2

Event Details	Start Date	End Date	Number of Nights	Billing Period(s)
Event A	17/05/2017	19/05/2017	2	2
Event B	12/07/2017	17/07/2017	4	2 nights in billing period 3, 2 nights in billing period 4

<tbody>
</tbody>

What I would like to do is show which billing period(s) the events in sheet2 fall under, based on the data in sheet1 (The answer for Event A would be billing period 2 only. The answer to Event B is much more complex)

Each "Event" occurs at night, Sunday - Thursday ONLY. Based on this, Event B would run Wednesday, Thursday, Sunday and Monday evenings. Hence a four night event for Event B.

Event B "straddles" over two billing periods (3 and 4). The first two nights would be in billing period 3 (Wednesday 12th July 2017, Thursday 13th July 2017). The second two nights would be in billing period 4 (Sunday 16th July 2017, Monday 17th July 2017)

The table in sheet2 shows how the answers would be displayed, but I am very open to easier ways of displaying my results. The critical thing is that I need to know how many of the total number of nights, fell within which billing period.

I would happily do this manually, but I have around 800 events to review

Thanks in advance to anyone who can help!