Date within date range query

[Microsoft]

Hi All,

Sheet1

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Billing Period[/TD]
[/TR]
[TR]
[TD]01/01/1900[/TD]
[TD]15/05/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16/05/2017[/TD]
[TD]15/06/2017[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]16/06/2017[/TD]
[TD]14/07/2017[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]15/07/2017[/TD]
[TD]15/08/2017[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]16/08/2017[/TD]
[TD]15/09/2017[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2

[TABLE="width: 500"]
<tbody>[TR]
[TD]Event Details[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Number of Nights[/TD]
[TD]Billing Period(s)[/TD]
[/TR]
[TR]
[TD]Event A[/TD]
[TD]17/05/2017[/TD]
[TD]19/05/2017[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Event B[/TD]
[TD]12/07/2017[/TD]
[TD]17/07/2017[/TD]
[TD]4[/TD]
[TD]2 nights in billing period 3, 2 nights in billing period 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!