Calculate holiday rate across holiday seasons
 

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

Calculate holiday rate across holiday seasons
 

Hi Steve,

Am Wed, 18 Mar 2020 11:29:24 -0700 (PDT) schrieb Evets Snomis:

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

did you get my mail?


Regards
Claus B.
--
Windows10
Office 2016

Calculate holiday rate across holiday seasons
 

Hi Claus

great to hear from you. I didn't get your email, I've probably changed addresses and the one registered on here isn't my live one.

Try this until we can communicate privately " evetsreg at outlook.com "

Steve