I have a tricky calculation I need to perform . . . I have the following data (this is a truncated version of it)

Season Start End Rate
--------------------------------------
Peak 1 01/01/2020 05/01/2020 1210
Peak 2 01/06/2020 31/08/2020 1210
Mid 01/09/2020 31/10/2020 1034
OffPeak 06/01/2020 31/05/2020 858



The data above is held in B7:D10 and are different holiday seasons and the rates charged

In B2 and C2 I have the arrival date and departure date which the formula below is referencing.

=LOOKUP(2,1/($B$7:$B$10<=$B$2)/($C$7:$C$10=$C$2),$D$7:$D$10)

At the moment it's giving me the correct number of days stay as long as the stay does not go across the 'seasons' for example from Peak to Mid.

I need a formula that will calculate how many days are in each season - for example, 30/8/2020 to 5/9/2020 would have some days in Peak and some in Mid season and, if possible, give me the appropriate rate for each.

TIA

Evets