1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • Calculate holiday rate across holiday seasons

    From Evets Snomis@21:1/5 to All on Wed Mar 18 11:29:24 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Fri Apr 3 12:05:11 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Evets Snomis@21:1/5 to All on Sat Apr 4 13:53:55 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)