Een contractperiode omzetten naar de respectievelijke delen per kalenderjaar

[Maser]

Beste allemaal,

Ik zit een beetje klem met de volgende vraag:

Ik heb een contractperiode; van een bepaalde datum tot een bepaalde datum (beiden los geadministreerd in de spreadsheet; bijv startdatum A1:A56000 en einddatum B1:B56000).

Deze data kan willekeurig zijn, echter de startdatum kan niet kleiner zijn dan 1/1/2015 en de einddatum niet groter dan 31/12/2020 zijn.

Nu wil ik van de betreffende van/tot datum het aantal maanden per jaar weten; dus een contract van 01/05/2015 tot 30/09/2018 kent 8 maanden in 2015; 12 maanden in 2016 etc.

Daarnaast wil ik graag van de desbetreffende jaren waarin het contract loopt de start van elk kalenderjaar weten; met als uitgangspunt dat als een contract een kalenderjaar overschrijdt, de start 1/1/20XX is. Dus hetzelfde contract van net (van 01/05/2015 tot 30/09/2018) kent een start in 2015 van 01/05/2015; in 2016 een start op 1/1/2016; in 2017 een start op 1/1/2017 en in 2018 een start op 1/1/2018.

Ik heb talloze combinaties van ALS en ALS(en) gebruikt maar op de één of andere manier loopt ik steeds vast:evil:

Wie o wie kan mij helpen??!

Alvast super bedankt! gr Jorg

 

[plongske]

Welkom op het forum.
Plaats eens een voorbeeld bestandje

 

[Maser]

Bekijk bijlage Vraag forum.xlsx

Bedankt! Heb een bijlage erbij gezet.

 

[abevleeming]

Deel 1:

Bekijk bijlage Vraag forum.xlsx

 

[Maser]

Hoi Abevleming.

Bedankt voor je reactie! Komt alleen niet helemaal uit. De "looptijd in maanden (E3)" gaat niet goed, heb hier zelf deze formule maar even voor ingezet:

=DATUMVERSCHIL(C3;D3+1;"m")

en die werkt prima.

In jouw bestandje klopt de optelling voor de kalenderjaren niet helemaal, kan alleen niet achterhalen hoe dat komt (gek van die datumvelden ). Zo is het totaal van F3:K3; 30, echter de looptijd in maanden is (E3) 38. Maar er lijkt iig een oplossing nabij

 

[abevleeming]

=ALS(EN(JAAR($C3)<=F$1;JAAR($D3)>=F$1);ALS(JAAR($C3)=F$1;13-MAAND($C3);ALS(JAAR($D3)=F$1;MAAND($D3)+ALS(MAAND(D3)=12;0;1);12));0)

Dat zou m moeten worden. Het lastige is dat je tot 31-10 telt. Dat betekent eigenlijk dat deze maand gewoon mee telt. Vandaar de +1

 

[Maser]

Hoi Abevleeming,

bedankt! Dat is het! Alleen de +1 is niet nodig, dan telt ie er juist eentje teveel. Maar zonder +1 gaat het helemaal prima!

De code voor (E3) "looptijd in maanden" is nu: =DATUMVERSCHIL(C3;D3+1;"m")
en de code voor de maanden per kalenderjaar is nu: =ALS(EN(JAAR($C3)<=F$1;JAAR($D3)>=F$1);ALS(JAAR($C3)=F$1;13-MAAND($C3);ALS(JAAR($D3)=F$1;MAAND($D3);12));0)

en dat werkt! Deel 1 van mijn puzzel is dus helemaal opgelost. Nu deel 2 nog..

 

[abevleeming]

Ik vermoedt dat daar wel een draaitabel voor te maken is... Ik ben daar alleen zelf echt geen held in..

 

[Maser]

Ben eruit.

Het is deze formule: =ALS(EN(F3>0;G3>0);$N$1;ALS(EN(F3=0;G3>0);$C3;ALS(EN(F3=0;G3=0);"";ALS(EN(F3>0;G3=0);""))))

Waarbij geldt dat de velden in deze formule op de volgende manier correleren met het voorbeeld bestand:

G=start=C
F=eind=D
AE=F
AF=G
AL=N

Nog te volgen?