Hulp nodig bij formule

davedeklerk · 1 jun 2020

Goedendag,

In bijgaand bestand wil ik in de kolom F een aantal punten berekenen, op basis van de begindatum.
Indien de begindatum ligt vóór 1 januari 2019, dan dient uit te worden gegaan van 1 januari 2019.
Als de datum ligt na 1 januari dan, uiteraard vanaf 1 januari. Er moet ook rekening worden gehouden met de einddatum.
Ik kom er niet helemaal uit met datumverschil.

Voorbeeld: Begindatum 01-01-2019 einddatum 31-12-2019 = 50 punten.
Begindatum 01-01-2019 einddatum 31-03-2019 = 12,5 punten.
Begindatum 01-01-2019 einddatum 02-04-2019 = 12,78 punten.

alphamax · 1 jun 2020

Als de datum ligt na 1 januari dan, uiteraard vanaf 1 januari

klopt dit?

Voorbeeld: Begindatum 01-01-2019 einddatum 31-12-2019 = 12 punten.
Begindatum 01-01-2019 einddatum 31-03-2019 = 3 punten.

Zijn de punten het aantal maanden?
Zo ja!

Begindatum 01-01-2019 einddatum 02-04-2019 = 3,03.

Dan klopt dit niet.

Kortom het is geen samenhangend geheel.
Maak eens een paar voorbeeldberekeningen met de hand.

davedeklerk · 1 jun 2020

alphamax zei:
klopt dit?
Zijn de punten het aantal maanden?
Zo ja!

Dan klopt dit niet.

Kortom het is geen samenhangend geheel.
Maak eens een paar voorbeeldberekeningen met de hand.

Het is een voorbeeld. In werkelijkheid gaat het om 50 punten op jaarbasis.
Dit dient te worden berekend naar rato van het aantal maanden, en dus ook per dag.
Dus volgens mij is 01-01-2019 t/m 02-04-2019 = 3,03 maanden.

alphamax · 1 jun 2020

Begindatum 01-01-2019 einddatum 31-12-2019 = 12 punten.

Is een heel jaar.

50 punten op jaarbasis

Hoe kunnen 12 punten nu 50 punten zijn?
---

Indien de begindatum ligt vóór 1 januari 2019, dan dient uit te worden gegaan van 1 januari 2019.
Als de datum ligt na 1 januari dan, uiteraard vanaf 1 januari.

Dus altijd 1 januari 2019?

davedeklerk · 1 jun 2020

alphamax zei:
Is een heel jaar. Hoe kunnen 12 punten nu 50 punten zijn?
---
Dus altijd 1 januari 2019?

Misschien ben ik niet helemaal duidelijk geweest, ik heb mijn vraag dan ook aangepast.

AlexCEL · 1 jun 2020

Begindatum 01-01-2019 einddatum 02-04-2019 = 3,03.
januari, februari, maart = 3 punten
2 april = 1/30 = 0,03 punten
Totaal = 3,03

Correct?

Mag het resultaat afgerond worden? Een dag in mei is 1/31 is afgerond ook 0,03.
Kan de begindatum ook na 1-1-19 liggen?

davedeklerk · 1 jun 2020

AlexCEL zei:
Begindatum 01-01-2019 einddatum 02-04-2019 = 3,03.

januari, februari, maart = 3 punten
2 april = 1/30 = 0,03 punten
Totaal = 3,03

Correct?

Ik heb mijn vraag al aangepast.
Misschien is het dan duidelijker. 50 punten op jaarbasis. 1 januari tot en met 2 april is volgens mij 3,03 maanden.

alphamax · 1 jun 2020

Voorbeeld: Begindatum 01-01-2019 einddatum 31-12-2019 = 50 punten.
Begindatum 01-01-2019 einddatum 31-03-2019 = 12,5 punten.
Begindatum 01-01-2019 einddatum 02-04-2019 = 12,78 punten.

Reken eens voor, hoe kom je aan al deze waarden?

davedeklerk · 1 jun 2020

AlexCEL zei:
Begindatum 01-01-2019 einddatum 02-04-2019 = 3,03.
januari, februari, maart = 3 punten
2 april = 1/30 = 0,03 punten
Totaal = 3,03

Correct?

Mag het resultaat afgerond worden? Een dag in mei is 1/31 is afgerond ook 0,03.
Kan de begindatum ook na 1-1-19 liggen?

Ja de datum kan inderdaad ook na 01-01-2019 liggen. Afronden liever niet.

AlexCEL · 1 jun 2020

Geen antwoord op mijn vraag of de berekening zoals in #6 klopt.

Om alphamax te herhalen:

Reken eens voor hoe kom je aan al deze waarden.

En dan ook nog voor bijvoorbeeld:
Begindatum 15-2-19; einddatum 16-5-19
Begindatum 10-4-19; einddatum 1-9-19

alphamax · 1 jun 2020

Zo zou ik het doen (misschien totaal niet wat jij wil), in F2

Code:

=50*(D2-C2+1)/(DATUM(JAAR(D2);12;31)-DATUM(JAAR(C2);1;1)+1)

davedeklerk · 1 jun 2020

AlexCEL zei:
Geen antwoord op mijn vraag of de berekening zoals in #6 klopt.

Om alphamax te herhalen:

Begindatum 01-01-2019 einddatum 31-12-2019 = 50 punten. Het aantal punten voor een heel jaar is 50.
Begindatum 01-01-2019 einddatum 31-03-2019 = 12,5 punten. 50/12*3=12,5
Begindatum 01-01-2019 einddatum 02-04-2019 = 12,78 punten. het is tot en met 02-04-2020. 2 dagen van 30 dagen = 0,066 x (50/12) = 0,277 + 12,5 = 12,77

alphamax · 1 jun 2020

Hoe zit het met een begindatum in een maand met 28/29/31 dagen?

AlexCEL · 1 jun 2020

Ik zit nu op zoiets:

Code:

=(50/12)*DATUMVERSCHIL(MIN("31-12-2019";MAX(C2;"1-1-2019"));1+MIN("31-12-2019";MAX(D2;"1-1-2019"));"m")+(DATUMVERSCHIL(MIN("31-12-2019";MAX(C2;"1-1-2019"));1+MIN("31-12-2019";MAX(D2;"1-1-2019"));"md")/DAG(LAATSTE.DAG(MIN("31-12-2019";MAX(D2;"1-1-2019"));0)))

En haak nu af. Al die uitzonderingen inbouwen wordt zeer complex...

M@rcel · 1 jun 2020

Zijn het berekening voor een heel kalenderjaar? Mocht dit zo zijn dan moet je gaan zoeken in de hoek van de systeemberekeningen van een "verhuurmodule". Ik zal dan even moeten zoeken of ik het bestandje kan vinden dat ik gemaakt heb tijdens mijn detacheringsperiode. Want je betaald per dag in meer in de maanden met 28/29/30 dagen dan in de maanden met 31 dagen. Hoe dit opgelost werd was een wegingsfactor toekennen aan de regels.

popipipo · 1 jun 2020

Om alles niet zo moeilijk te maken dan kun je toch veel eenvoudiger de volgende berekening toepassen:
50/365=0,137
Zo staat elke dag dus voor 0,137 punten
Het probleem van meer of minder dagen in de maand is dan meteen opgelost.

M@rcel · 1 jun 2020

davedeklerk zei:
Begindatum 01-01-2019 einddatum 31-12-2019 = 50 punten. Het aantal punten voor een heel jaar is 50.
Begindatum 01-01-2019 einddatum 31-03-2019 = 12,5 punten. 50/12*3=12,5
Begindatum 01-01-2019 einddatum 02-04-2019 = 12,78 punten. het is tot en met 02-04-2020. 2 dagen van 30 dagen = 0,066 x (50/12) = 0,277 + 12,5 = 12,77

@popipipo: Hieruit maak ik op dat het juist niet bedoeling is om de verschillen per maand gelijk te trekken. Hierdoor zal je formule toch een stuk complexer worden dan je eigenlijk zou willen. TS zal voor hem het besluit moeten nemen hoe hij de berekening wil hebben. In post 1 wordt aangegeven dat 3 maanden 1/4 van het puntentotaal is, terwijl als het 3 maanden en twee dagen dit naar rato moet zijn. Dit zijn twee verschillende rekenmethodes met verschillende uitkomsten, namelijk:

1/4 x 50 = 12,50 ( 01-01-2019 t/m 31-03-2019 )
90/365 x 50 = 12,32877 ( 01-01-2019 t/m 31-03-2019 )

popipipo · 1 jun 2020

De manier waarop de TS het wil loopt wat mij betreft aan alle kanten schreef.

1 dag in april geeft dus meer punten dan 1 dag in maart.
1 dag in april geeft weer minder punten dan 1 dag in februari.

Voor mij is elke dag in het jaar gelijk.

M@rcel · 1 jun 2020

popipipo zei:
De manier waarop de TS het wil loopt wat mij betreft aan alle kanten schreef.

1 dag in april geeft dus meer punten dan 1 dag in maart.
1 dag in april geeft weer minder punten dan 1 dag in februari.

Voor mij is elke dag in het jaar gelijk.

Ik ben het hierin wel met je eens, maar als je in het geval een woningcorporatie een voorschot van servicekosten in rekening dan zegt het onafhankelijk orgaan de huurcommissie dat je de voorschotbedragen per maand moet verdelen naar dag. En dan kom je uit op de maanden die geen 31 dagen hebben dat je meer per dag betaald dan in de maanden met 31 dagen.

Vervolgens spreken zij zichzelf weer tegen op het moment dat je de werkelijke kosten moet gaan afrekenen met de huurders: Dan moet je de totale jaarlijkse kosten verdelen oven het aantal dagen dat een huurder er heeft gewoond.

Ik vermoed, omdat het over een puntentoekenning gaat, dat je moet gaan denken aan:

Je krijgt 50 punten per jaar. En ben korter lid, dan betaal je naar verhouding van het aantal dagen. En dan ben je snel klaar:

Aantal dagen lid
-------------------
365 dagen x 50 punten = aantal toegekende punten voor dit jaar

Hulp nodig bij formule

Gebruiker

Bijlagen

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Meubilair

Gebruiker

Terugkerende gebruiker

Gebruiker

Meubilair

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Meubilair

Gebruiker

Meubilair

Gebruiker

Meubilair

Gebruiker

Wij waarderen jouw privacy