[Excel] Periode berekenen als deel van een jaar

[Anoniem: 1115201]

Voor een verlof/snipperdagen rapportage voor mijn collega's ben ik op zoek naar een oplossing/ formule voor onderstaand probleem.

uiteindelijk wil ik een indicatie hoeveel verlof uren iemand per jaar krijgt. Als iemand net in dienst is bouwt deze een gedeelte van het jaar verlof dagen op. Om dit te berekenen heb ik het aantal dagen in dienst nodig. Het moet een als dan formule worden.
BV: ALS het aantal dagen < 365.25 gedeelte van het aantal dagen als > 365.25 dan volledige rechten.

B7 C7 Aantal dagen in dienst.
In Dienst 13-08-2018 ????

De moeilijkheid zit hem hier in het feit dat de formule op elk moment in elk jaar stand moet houden.
Het enige wat ik wil gebruiken is de datum van in dienst treding. Dus geen hulp notatie velden voor het makkelijk uitrekenen.

Dus Bij in dienst : 13-08-2018 moet hij vanaf 01-01-2018 tellen
en in dienst vanaf 01-05-2017 moet hij vanaf 01-01-2017 tellen.

[Panini]

Ten eerste: welkom op Tweakers. *knip*: Cru ziet geen rode nick. Gebruik dus een topicreport als je vindt dat het topic ondermaats is. Thx
Bereken je dit soort dingen elk jaar weer trouwens? Zijn vrij standaard features die praktisch elk type roostersoftware wel heeft. Misschien is het eens tijd om daar naar te kijken.

[ Voor 62% gewijzigd door Lustucru op 12-09-2018 14:43 ]

[GforceNL]

(aantal verlof dagen per jaar) / (aantal dagen per jaar) x (dagen gewerkt) = (recht op verlof)

[Tarin]

Hoe complex wil je het maken? Gewoon aantal dagen tot einde van het jaar gedeeld door 365,25 maal aantal verlofdagen per vol jaar?
Of wil je terugrekenen hoeveel werkdagen (ma-vr uitgaande van een 5x8-schema) er daadwerkelijk in de rest van het jaar zitten minus de verplichte vrije dagen als die (gedeeltelijk) van je vakantiedagen in mindering gebracht worden?

[Anoniem: 1115201]

Dank jullie wel voor jullie snelle reacties.
Ik heb alle formules al klaar. Voor onderstaande waarde heb ik nog een formule nodig.

Aantal dagen in dienst. of gedeelte van het jaar.
Met de enige waarde die gegeven word is datum in dienst. 13-08-2018 moet dus als uitkost 30 geven.

In formule vorm zou dit er ongeveer zo uit moeten zien:
= "datum in dienst" tot 31 december van opgegeven jaar. = aantal dagen in dienst.

Dit gedeelte voeg ik dan tussen de formule die ik al heb.
=ALS((DATUMVERSCHIL(C7;(VANDAAG());"d"))<365,25;99999;ALS((DATUMVERSCHIL(C7;(VANDAAG());"d"))>365,25;88888))

op plaats 99999 moet de gevraagde formule komen.
op plaats 88888 komt de verwijzing naar de cel met de formule voor de volledige jaar rechten.

[thoenk]

Je hebt je vraag gepost onder 'excel', dus daar ben ik voor het gemak maar vanuit gegaan. Ooit gehoord van functies/formules/formulas in excel? dat maakt je leven een stuk makkelijker. Eentje heet (in het Engels): "NETWORKDAYS()". Begin en einddatum erin en je hebt je werkdagen. Doe dit nog een keer vanaf een tweede datum (bijv 1 januari) en je kan deze waarden gebruiken om het totaal aantal verlof dagen te berekenen.
Met een IF loop kun je voor andere beginjaren een andere berekening laten uitvoeren.
edit: aanpassing

[Tarin]

Wat @thoenk zegt, Excel heeft ook al een paar slimmigheidjes ingebouwd, want je bent niet de eerste die zoiets uit wil rekenen .
Blader deze lijst (of als je gericht zoekt deze) eens door, je vindt vast het een en ander wat je veel tijd kan besparen .

[Anoniem: 1115201]

Dank je wel Tarin, Ik heb even gezocht.

Ik ben op zoek naar iets dergelijks als de: LAATSTE.DAG(begindatum;aantal_maanden) formule.

Maar dan voor laatste dag van het jaar zonder dat "aantal_maanden" ingegeven moet worden.. Deze kan ik niet vinden.

[Tarin]

De laatste dag van het jaar krijg je met die formule als je als "aantal_maanden" het aantal resterende maanden in het jaar ingeeft. Voor een datum in augustus dus 12-8=4. Is vast ook te berekenen, maar zal in de regel uitkomen op 31-12-[jaar] .

[ Voor 18% gewijzigd door Tarin op 12-09-2018 12:07 ]

[Anoniem: 1115201]

Hallo Tarin,

Dank je, Dat begrijp ik. Maar deze lijst gaat naar meerdere vestigingen en moet mijn collegas die dit bij gaan houden duidelijk maken dat ze bij elke keer als ze een nieuwe collega in de lijst zetten de waarde in een formule gaan aanpassen. Dat gaat zeker fout. Vandaar dat ik zoek naar een FOOLPROOVE formule.

Dan is het invoeren van een nieuwe collega kinderspel.

Maar ja nu die formule nog. Ik krijg het idee dat Excel hier geen oplossing/ mogelijkheid voor heeft.

[Stoelpoot]

Anoniem: 1115201 schreef op woensdag 12 september 2018 @ 12:28:
Hallo Tarin,

Dank je, Dat begrijp ik. Maar deze lijst gaat naar meerdere vestigingen en moet mijn collegas die dit bij gaan houden duidelijk maken dat ze bij elke keer als ze een nieuwe collega in de lijst zetten de waarde in een formule gaan aanpassen. Dat gaat zeker fout. Vandaar dat ik zoek naar een FOOLPROOVE formule.

Dan is het invoeren van een nieuwe collega kinderspel.

Maar ja nu die formule nog. Ik krijg het idee dat Excel hier geen oplossing/ mogelijkheid voor heeft.

Je zou er een macro voor kunnen maken als je echt bij excel wilt blijven. Maar ik denk ook dat als je echt een foolproof systeem wilt, een Excel-sheet waar gebruikers alles in kunnen aanpassen nooit de oplossing zal zijn.

[breew]

Dit zou moeten werken. Werkt ook goed in schrikkeljaren, dus die 365,25 mag in de prullenmand..

(man, wat haat ik dit soort lange, onoverzichtelijke, geneste formules!!)

=ALS.FOUT(ALS(A2<DATUMWAARDE("01-01-"&JAAR(VANDAAG()));1;DATUMVERSCHIL(A2;DATUMWAARDE("31-12-"&JAAR(VANDAAG()));"D")/DATUMVERSCHIL(DATUMWAARDE("1-1-"&JAAR(VANDAAG()));DATUMWAARDE("31-12-"&JAAR(VANDAAG()));"D"));0)

Functioneel

ligt de datum voor 1-1 van het huidige jaar? 
   ja: uitkomst = 1
   nee: hoe groot is het verschil (in dagen) tussen de datum in dienst en 31-12 van het huidige jaar
          deel de uitkomst door het aantal dagen in het huidige jaar
          Ligt de datum in dienst na 31-12 van het huidige jaar (genereert een fout): uitkomst = 0

berekent het percentage verlof 0 = geen, 1 = 100%

Lijkt prima te werken in onderstaand POC:

[ Voor 41% gewijzigd door breew op 12-09-2018 13:00 ]

[Triggy]

Dat kan korter

code:

=MAX(0;MIN(365;DATUM(JAAR(NU());12;31)-A2)/365)

Met in cel A2 de datum.

NU() = de datum en tijd van vandaag.

DATUM(JAAR(NU());12;31) = 31 december van dit jaar.

DATUM(JAAR(NU());12;31)-A2 = aantal dienstdagen sinds de datum in cel A2 tot het einde van dit jaar.

MIN(365; ... ) = aantal dienstdagen dit jaar voor mensen die langer dan een jaar in dienst zullen zijn aan het eind van het jaar.

MAX(0; ... ) = aantal dienstdagen dit jaar voor mensen die na het eind van dit jaar in dienst komen.

/ 365 = Dienstdagen %

Succes!

[breew]

Triggy schreef op woensdag 12 september 2018 @ 13:09:
Dat kan korter

code:

=MAX(0;MIN(365;DATUM(JAAR(NU());12;31)-A2)/365)

Met in cel A2 de datum.

NU() = de datum en tijd van vandaag.

DATUM(JAAR(NU());12;31) = 31 december van dit jaar.

DATUM(JAAR(NU());12;31)-A2 = aantal dienstdagen sinds de datum in cel A2 tot het einde van dit jaar.

MIN(365; ... ) = aantal dienstdagen dit jaar voor mensen die langer dan een jaar in dienst zullen zijn aan het eind van het jaar.

MAX(0; ... ) = aantal dienstdagen dit jaar voor mensen die na het eind van dit jaar in dienst komen.

/ 365 = Dienstdagen %

Succes!

toon volledige bericht

schikkeljaar != 365 dagen... maar als dat kleine verschil niet uitmaakt, dan werkt dit natuurlijk ook uitstekend!

[ Voor 5% gewijzigd door breew op 12-09-2018 13:12 ]

[Triggy]

Uiteraard, foutje van 1/365, kan tot maximaal 1 dag afrondingsverschil leiden mochten er 365 vakantiedagen per jaar zijn .

[eric.1]

Compleet (nouja, gedeeltelijk) offtopic; heeft HR geen software in gebruik waar dit al lang en breed in zit?

Kan me niet voorstellen dat de salaris/medewerkers-administratie en (vakantie)planning allemaal in een excelblaadje ingevoerd worden.

[Laurensius88]

eric.1 schreef op woensdag 12 september 2018 @ 13:26:
Compleet (nouja, gedeeltelijk) offtopic; heeft HR geen software in gebruik waar dit al lang en breed in zit?

Kan me niet voorstellen dat de salaris/medewerkers-administratie en (vakantie)planning allemaal in een excelblaadje ingevoerd worden.

/volledig-off-topic Dat zal je je nog verbazen hoeveel bedrijven er met een excelletje werken voor dat soort dingen

[Anoniem: 1115201]

Super bedankt heren,

Werkt prima! mijn probleem is opgelost!

[Lustucru]

Anoniem: 1115201 schreef op woensdag 12 september 2018 @ 13:36:
Super bedankt heren,

Werkt prima! mijn probleem is opgelost!

Top! Verzin je een volgende keer ook een titel zonder uitroeptekens en met meer informatie? Bijvoorbeeld:
'[Excel] Periode berekenen als deel van een jaar'. Rekenen met tijd is erg algemeen en gevorderd is het zeker niet.

Lees ook even de Het algemeen beleid #quickstart. Daar lees je wat we van een topicstart verwachten en hoe je een geode topicstart maakt. Voorkomt offtopic reacties en slotjes.

[Tarin]

Laurensius88 schreef op woensdag 12 september 2018 @ 13:33:
[...]


/volledig-off-topic Dat zal je je nog verbazen hoeveel bedrijven er met een excelletje werken voor dat soort dingen

/nog erger off-topic: bij mijn vorige baan was het een schriftje en een pen.

[Lustucru]

breew schreef op woensdag 12 september 2018 @ 12:55:
Dit zou moeten werken. Werkt ook goed in schrikkeljaren, dus die 365,25 mag in de prullenmand..

(man, wat haat ik dit soort lange, onoverzichtelijke, geneste formules!!)

=ALS.FOUT(ALS(A2<DATUMWAARDE("01-01-"&JAAR(VANDAAG()));1;DATUMVERSCHIL(A2;DATUMWAARDE("31-12-"&JAAR(VANDAAG()));"D")/DATUMVERSCHIL(DATUMWAARDE("1-1-"&JAAR(VANDAAG()));DATUMWAARDE("31-12-"&JAAR(VANDAAG()));"D"));0)

Triggy schreef op woensdag 12 september 2018 @ 13:09:
Dat kan korter

code:

=MAX(0;MIN(365;DATUM(JAAR(NU());12;31)-A2)/365)

Nog korter (of iig eleganter imho)

code:

=MIN(A1<nu();JAAR.DEEL(A1;DATUM(JAAR(NU());12;31))

En dan gaan schrikkeljaren mee en kun je nog kiezen hoe je een jaar wilt berekenen. Wat iemand al opmerkte; Excel heeft heel veul dingen al ingebouwd.

[ Voor 44% gewijzigd door Lustucru op 12-09-2018 15:40 . Reden: mierenneukers blijf je houden :+ ]

[Triggy]

Lustucru schreef op woensdag 12 september 2018 @ 14:54:

code:

=MIN(1;JAAR.DEEL(A1;DATUM(JAAR(NU());12;31))

Probleem is wel dat JAAR.DEEL geen negatief resultaat kan geven. Iemand die volgend jaar in dienst komt heeft dan dit jaar al recht op vakantie

[Lustucru]

Dat heeft hij ook. Het wordt alleen betaald door een ander.

maar vooruit, zie mijn edit ^^

[ Voor 43% gewijzigd door Lustucru op 12-09-2018 15:40 ]

[Anoniem: 1115201]

Super bedank!

Met jullie hulp staat een mooi Excel bestand klaar.

Een mooi werk baar excel bestand voor verwerking en rapportering van snipperdagen , verjaardagen van personeel partners en kinderen , trouwdagen, in dienst jubileums etc.

Wie dit bestand wil hebben laat maar een berichtje achter. :-)

m.v.g. Jeroen

[Anoniem: 1115201]

Mijn Excuus!

Dezelfde vraag alleen wil ik nu als antwoord uit de formule niet het aantal dagen van af die datum tot het eind van dat jaar.

Maar het aantal maanden tot het eind van dat jaar.
??? Ik ben en flink aan het proberen maar kom er niet uit. Wie kan me op weg helpen?

[Anoniem: 1115201]

Is er iemand die mij op weg kan helpen?

[F_J_K]

offtopic:
@Anoniem: 1115201 je wilt bij een nieuwe concrete vraag een nieuw topic openen. En dan bij de vraag ook meteen zeggen wat je zelf hebt geprobeerd, waar je concreet vastloopt. (Dus niet "ik heb veel geprobeerd" maar "ik heb dacht aan functie(x,y,z123)+anderefunctie(a,b,c) maar loop vast omdat.."). Al is dit wel erg in het verlengde, kan hier wel verder.

Dan resteert wel de tip: geef concreet aan waar je dan vastloopt. Ik kan me voorstellen dat mensen afhaken als het eenrichtingsverkeer lijkt. Vandaar.

Wil je dat het vanaf vandaag (2okt) nog 3 maanden is (naar boven afronden), 2 (naar beneden), of 2 maanden, 29 dagen en misschien ook 9 uur en 50 minuten? Hoe dan ook: je kunt de eerdergenoemde gedachten opsplitsen naar tellen hoeveel dagen er nog zijn tot einde maand, en dan het aantal maanden (12 - huidige maand).

[Anoniem: 1115201]

een voorbeeld en goed antwoord zou zijn: (gevraagd is uiteindelijk de excelformule die dat antwoord geven kan)

Iemand komt 15-10-2018 in dienst dan heeft hij tot het einde van het jaar 2,5 maanden recht op een gedeelte van een aantal snipperdagen per jaar. Stel het jaar totaal jaar recht is 296 uren snipperdagen.
De uitkomst zou dus 61.66 moeten zijn.

Ik kan begrijpen dat het niet mogelijk is om met gedeeltes van maanden te rekenen een goede 2de keus zou dan met een startdatum van 15-10-2018 de uitkomst 73.99 zijn.

[Anoniem: 1115201]

Iemand een idee? :-)

[Lustucru]

Ik herhaal even letterlijk wat @F_J_K je ook al heeft gemeld:

offtopic:
@Anoniem: 1115201 je wilt bij een nieuwe concrete vraag een nieuw topic openen. En dan bij de vraag ook meteen zeggen wat je zelf hebt geprobeerd, waar je concreet vastloopt. (Dus niet "ik heb veel geprobeerd" maar "ik heb dacht aan functie(x,y,z123)+anderefunctie(a,b,c) maar loop vast omdat.."). Al is dit wel erg in het verlengde, kan hier wel verder.

Dan resteert wel de tip: geef concreet aan waar je dan vastloopt. Ik kan me voorstellen dat mensen afhaken als het eenrichtingsverkeer lijkt. Vandaar.

[Anoniem: 1115201]

Beste Lusttucru , F_J_K,

kunnen jullie mij misschien nog uitleg geven? Het is voor mij nog niet helemaal duidelijk.

[F_J_K]

Geef dan eerst aan waar je nu vastloopt Het lijkt een beetje alsof je achterover wilt leunen en iemand het helemaal uitschrijft. Klinkt misschien wat jammer, maar ik ga (ook omdat fouten maken te makkelijk is en ik dan je collegas's benadeel) de concrete Excelfunctie dan niet geven. Althans niet zonder afspraak over het uurtarief Wel denk ik graag mee. En dat geldt volgens mij ook voor anderen. Ook leerpuntje voor volgende topics: geef in de topicstart al eigen gedachten.

Dus: welke functies heb je nu geprobeerd, waar loop je dan vast? Hoe doe je het op papier en valt dat niet te vertalen naar Excel-formules?
Met de gedachte van de al gegeven functie kom je volgens mij al een heel eind, afhankelijk van de concrete details van het beleid. Eventueel aangevuld met =Maand(nu()) zodat je weet welke maand het nu is, wetende dat er 12 maanden in het jaar zijn. Idem de dag. Maar Anoniem: 1115201 in "[Excel] Periode berekenen als deel van een jaar" suggereert dat het aantal maanden helemaal niet relevant is, alleen het aantal dagen en die functie was al gegeven.

offtopic:
Lustucru in "[Excel] Periode berekenen als deel van een jaar" had een kleine denkfout. Jaar.deel() rekent namelijk standaard met dagen van 360 dagen. Al hoop ik dat je https://support.office.co...6d-4143-82b6-208454ddc6a8 al hebt gelezen en dus weet dat =MIN(A1<NU();JAAR.DEEL(A1;DATUM(JAAR(NU());12;31);1)) beter zal passen, dus met ;1.

[Anoniem: 1115201]

Beste F_J_K en Lustucru,

Bedankt voor jullie knowhow en tijd. Ik heb me moeten verdiepen in de werkwijze van de door jullie gegeven functie. Ik begrijp hem nu. Dank je wel voor het duwtje dat ik me even moest verdiepen! :-)

Ik heb de functie aangepast en verdeeld in 2 formules zoals onderstaand. maar als ik ze samen wil voegen krijg ik een andere uitkomst. Ik vermoed dat het te maken heeft dat er met datums gerekend wordt.

D9 = Datum in dienst treding. bv 13-08-2018

=(1-(DAG(D9)/DAG(LAATSTE.DAG(D9;0))))+(12-(MAAND(D9)))
uitkomst bovenste formule = 4,58 Maanden tot het einde van het jaar.

=(((((12+25)*8)/40)*D10)/12)
Uitkomst bovenste formule = 24.67
waarin 12 ATV Dagen 25 Snipperdagen = 296 uur per jaar / 12 = 24,67 uur per maand.

Deze formules samen gevoegd moet de uitkomst geven 112.99 Uren recht op verlof tot het einde van dat jaar.

Maar als ik ze nu samen voeg krijg ik als uitkomst : 18,32

Wat gaat er mis?

[loeberce]

Als je de samengevoegde formule ook even post dan kunnen we zien waar het niet goed gaat. Soms is dat zoiets simpels als een haakje op een andere plek.

[Anoniem: 1115201]

=(((((12+25)*8)/40)*D10)/12)*(1-(DAG(D9)/DAG(LAATSTE.DAG(D9;0))))+(12-(MAAND(D9)))

[TheFes]

Anoniem: 1115201 schreef op vrijdag 12 oktober 2018 @ 12:39:
=(((((12+25)*8)/40)*D10)/12)*(1-(DAG(D9)/DAG(LAATSTE.DAG(D9;0))))+(12-(MAAND(D9)))

Ik geloof dan Meneer Van Dalen Wacht Op Antwoord tegenwoordig niet meer van toepassing is, en dat er een andere regel toegepast moet worden, maar dat is hier in ieder geval wel het probleem.

Je moet even haakjes om de tweede formule zetten.

Nu doet hij eerst deel A*B en dan pas + deel C
Je wil A * (B+C)

Overigens lijk je al wel een overschot aan haakjes te hebben, er kunnen er volgens mij wel een paar weg..

[Anoniem: 1115201]

Natuurlijk. Ik zie het nu pas! Tanks!