EXCEL Teriefsberekening afhankelijk van ingegeven datumreeks

[Eric H]

Mijn vraag

Voor reserveringen wil ik Excel laten uitrekenen wat de kosten zijn van boekingen afhankelijk van of de boeking in het hoog- laag- of middenseizoen plaats vindt.

Ik heb in een Excel blad diverse van-tot reeksen gedefinieerd met daarachter het geldende tarief voor die periode.
Kolom A = datum vanaf
Kolom B = tot en met datum
Kolom C = tarief voor die periode

In een ander tabblad noteer ik :
Kolom A : vanaf datum reservering
Kolom B : tot en met datum reservering

Vervolgens wil ik dan dat in Kolom C uitgerekend wordt wat de kosten zijn voor die periode.
Wat het wellicht lastig maakt is dat reserveringen kunnen doorlopen in verschillende periodes.

Oftewel een boeking begint in het middenseizoen (met daarbij behorend tarief) maar loopt door tot een datum in het hoogseizoen (met daarbij dus een ander tarief)

Misschien denk ik te moeilijk, maar ik kom er zo 1,2,3 niet uit.
...

Relevante software en hardware die ik gebruik
Excel
...

Wat ik al gevonden of geprobeerd heb
Ik heb al een Google poging gedaan maar heb moeite met de juiste zoekterm.
Tevens heb ik geprobeerd om het met een ALS functie te doen, maar hier kwam ik ook niet uit.

...

[Sirhc_95]

Er zijn natuurlijk meerdere wegen die naar Rome leiden, maar de overgang tussen het midden- en hoogseizoen is in principe toch altijd op een vaste datum voor een reservering van een x aantal weken?

Volgens mij zou je dan met de formule DATUMVERSCHIL het aantal dagen voor en na de datum waarop het tarief wijzigt kunnen berekenen en vervolgens deze aantallen dagen dan weer met de bijbehorende tarieven kunnen vermenigvuldigen? Dit zou zelfs in een grote formule kunnen per regel, dan heb je aan een tabblad genoeg.

[Eric H]

Probleem is alleen dat de seizoensopbouw wisselend is en door elkaar loopt.

Dus in het ene blad heb ik de seizoenen met de daarbij behorende periodes gespecificeerd :

Kolom A. Kolom B. Kolom C.
van. Tot en met. Tarief.
1-1-2023. 20-4-2023. 40
21-4-2023. 14-5-2023. 75
15-5-2023. 31-5-2023. 40
1-6-2023. 6-7-2023. 55
7-7-2023 3-9-2023. 75
4-9-2023. 12-10-2023. 55
13-10-2023 28-10-2023 75
29-10-2023 31-12-2023 40

En in een ander blad noteer ik de reserveringen :
Kolom A. Kolom B. Kolom C. Kolom D
Wie. Van datum. Tot en met datum. Te betalen bedrag.

Dus de formule die in "Te betalen bedrag" moet komen, moet dus rekening houden met het feit dat een klant een reservering kan doen die bijvoorbeeld bestaat uit 3 dagen tegen tarief 55 en 6 dagen tarief 75.
Bijvoorbeeld bij een reservering van 4-7-2023 tot en met 12-7-2023.

[OdeB]

Wellicht dat het met onderstaande formule eenvoudig is op te lossen:
=DAGEN(DATUM SEIZOEN 1;DATUM RESERVERING 1)*BIJBEHORENDE WAARDE +DAGEN(DATUM RESERVERING 2; DATUM SEIZOEN 2)*BIJBEHORENDE WAARDE

[Voor 11% gewijzigd door OdeB op 18-01-2023 00:31]

[Eric H]

OdeB schreef op woensdag 18 januari 2023 @ 00:30:
Wellicht dat het met onderstaande formule eenvoudig is op te lossen:
=DAGEN(DATUM SEIZOEN 1;DATUM RESERVERING 1)*BIJBEHORENDE WAARDE +DAGEN(DATUM RESERVERING 2; DATUM SEIZOEN 2)*BIJBEHORENDE WAARDE

Dat lijkt me ook niet goed te gaan aangezien ik niet zie hoe deze formule rekening houdt met overgang van seizoensprijs wanneer de vanaf en tot en met datum in verschillende seizoenen liggen.

[Patrick_6369]

Ik zou met hulpkolommen werken. Eén hulpkolom voor ieder tarief. In iedere hulpkolom bereken je met de tip van @Sirhc_95 hoeveel dagen er in die tariegroep vallen. Daarna is het een koud kunstje van vermenigvuldigen en optellen.

[Lustucru]

Om het eerst op te zetten met hulpkolommen is een goed plan zodat je doorgrondt hoe de berekening in elkaar zit. In principe kun je een overlap berekenen door van de kleinste van einde_periode/einde reservering de grootste van begin-periode/begin-reservering af te trekken en het eindresultaat te controleren op groter dan 0.

Je krijgt dan iets als MAX(0,MIN(pe,re)-MAX(pb,rb)). Als dat goed werkt kun je het met een matrixformule optimaliseren tot één korte formule voor alle periodes.

[Eric H]

Patrick_6369 schreef op woensdag 18 januari 2023 @ 08:31:
Ik zou met hulpkolommen werken. Eén hulpkolom voor ieder tarief. In iedere hulpkolom bereken je met de tip van @Sirhc_95 hoeveel dagen er in die tariegroep vallen. Daarna is het een koud kunstje van vermenigvuldigen en optellen.

Maar daarmee ondervang ik toch niet de reserveringen die een overlap hebben in verschillende tariefsperiodes ?
Dan weet ik alleen hoeveel dagen er in een bepaalde periode zitten en tegen welk tarief er berekend moet worden.

Maar in het door mij gestelde voorbeeld kun je niet rekenen met 1 tarief voor een totaalperiode die over twee tariefsperiodes loopt.
Oftewel, klant maakt reservering van 3 weken waarvan 10 dagen onder tarief a vallen en 11 dagen onder tarief b.

Of ik begrijp gewoon niet wat je bedoelt met hulpkolommen en hoe deze toe te passen.

[Patrick_6369]

Eric H schreef op woensdag 18 januari 2023 @ 09:45:
[...]Of ik begrijp gewoon niet wat je bedoelt met hulpkolommen en hoe deze toe te passen.

Om het dan maar even helemaal uit te werken met een fictief voorbeeld, op basis van tarieven die exact gelijklopen met de maanden en een reservering van 24 januari tot en met 7 maart. Dan krijg je een sheet die er in de basis als volgt uit ziet:



Achter de schermen zitten deze formules er dan achter:

[Eric H]

Ahhhh, er gaat hier een lampje branden.....

Wat je eigenlijk doet is een soort van "tussenberekening"
Nu is de uitdaging nog dat ik drie tarieven heb, maar die komen terug in meerdere delen van het jaar.
( zie mijn eerdere post)

Maar ik ga wel kijken of ik hier omheen kan werken.

EDIT:

Helaas, maar dit lijkt niet te werken.


Ik zet de 8 tariefsperiodes in een tabel (tarief 1 t/m 8 met de daarbij behorende periodes en waardes)
Vervolgens zet ik datum "van" op 8-4-2023 en datum "tot en met" op 15-4-2023

Bij berekening van Tarief1 krijg ik als uitkomst 8 (dat klopt dus)
Maar wanneer ik Tarief2 wil uitrekenen krijg ik -5 te zien....
------------------------------------------------------------------------------------------------
EDIT2:
Volgens mij heb ik dit opgelost met het volgende:
=MAX(0;jouw formule) toegevoegd.
Dit lijkt goed te gaan!

[Voor 53% gewijzigd door Eric H op 18-01-2023 13:21]

[Lustucru]

Eric H schreef op woensdag 18 januari 2023 @ 12:10:
Ahhhh, er gaat hier een lampje branden.....

EDIT2:
Volgens mij heb ik dit opgelost met het volgende:
=MAX(0;jouw formule) toegevoegd.
Dit lijkt goed te gaan!

En dan heb je dus wat hier ook al staat: Lustucru in "EXCEL Teriefsberekening afhankelijk van ingegeven datumreeks"