Dagen per maand bepalen aan hand van begin en einddatum

[Groentje19]

Goedemiddag allemaal,

Ik ben John, nieuw hier en ondanks dat ik al heel lang Excel heb voor de huis-tuin-en keukentabellen etc., ben ik nu echt aan het uitproberen wat er allemaal mee kan. Er gaat een wereld voor me open, dus, ambitieus als ik ben heb ik mezelf op een leuke manier in de nesten gewerkt door lekker een ingewikkelde werkmap te maken. Ik maak een afrekening servicekosten.

Dat klinkt simpel, maar dat is het niet, er spelen veel factoren mee en dat maakt het uitdagend. Een vraagstuk waarin ik me nu heb vastgebeten is het volgende:
Ik wil een begindatum en een einddatum invoeren. Ik zoek een formule die als resultaat per kalendermaand het aantal bewoonde dagen teruggeeft in de cel waar ik die wil hebben.
Zover gaat mijn kennis nog niet, iemand die me een duwtje in de goede richting kan geven?
Berekening aandeel bewoning
Eerste woondag jaar 1-1-2018
Laatste woondag jaar 31-12-2018

Factor bewoning dagen bewoond
Januari 2018 31
Februari 2018 28
Maart 2018 31 etc.

Maar als iemand dus op 5 januari erin gaat en op een willekeurige datum weer vertrekt, moet de formule ook het juiste aantal dagen geven, ook als dat in dezelfde maand zou vallen. Ik kan het natuurlijk heel makkelijk maken door handmatig de data in te voeren, maar als er een manier is om het via Excel te doen, dan zou ik die graag leren. Het document is slechts een middel om Excel te leren, maar het zou wel leuk zijn als het ook daadwerkelijk werkt.

 

[sylvester-ponte]

hallo Groentje, welkom op dit forum.

je hebt het over "Het document" , maar is zie nergens een excel voorbeeldje van je.

 

[Groentje19]

Het uploaden van de afbeelding lukte niet, nu wel.

Ik heb een voorbeeldbestandje bijgevoegd. De tabel is wat ik graag wil, in de rode kolom zou dan per maand het aantal bewoonde dagen moeten komen dat binnen de opgegeven data valt.

 

[JanBG]

Hallo,

Dat is toch niet zo ingewikkeld: kolom A: datum in, kolom B: datum uit en in kolom C:

B-A

 

[Groentje19]

Hallo,

Dat is toch niet zo ingewikkeld: kolom A: datum in, kolom B: datum uit en in kolom C:

B-A

Dat is niet de bedoeling, ik wil het leren met een formule voor de hele rode kolom, ook omdat er andere info aan gekoppeld gaat worden. Bijvoorbeeld kalenderjaar overschrijdende facturen, die terug gerekend worden naar een de daadwerkelijke kosten voor het betreffende kalenderjaar etc.

Ik moet dus met een formule bepalen of maand in A1 valt binnen het bereik van de beide data, zo ja, het aantal dagen bereken en plaatsen in de rode cel achter de maand, idem voor de daaropvolgende maand.
Ik zoek bewust niet de makkelijkste weg, de werkmap bestaat uit diverse gecompliceerde berekeningen, dus data entry en foutmarges wil ik zoveel mogelijk beperken.

 

[snb]

In E7:

=MIN($E$3;EOMONTH(DATE(YEAR($E$2);ROW(A2);1);0))-$E$2-SUM($E$6:$E6)

In E6:

=MIN($E$3;EOMONTH(DATE(YEAR($E$2);ROW(A1);1);0))-$E$2

 

[roeljongman]

Ik kwam tot de volgende formule

=ALS($E$3<LAATSTE.DAG(D6;0);$E$3;LAATSTE.DAG(D6;0)+1)-ALS($E$2<=D6;D6;$E$2)

waarbij de maanden in kolom D ook met formules worden bepaald.

Als je de formule analyseert in stukjes. moet je in gedachte houden dat je altijd de begindatum van de einddatum aftrekt.

ALS($E$3<LAATSTE.DAG(D6;0);$E$3;LAATSTE.DAG(D6;0)+1)
Als de laatste woondag kleiner is dan de laatste dag van de kalendermaand. gebruik dan de laatste woondag. en is de laatste woondag groter gebruik dan laatste dag van de kalendermaand
om het juist aantal dagen te krijgen moet er 1 bijgeteld worden

daar moet de begin datum van worden afgetrokken

-ALS($E$2<=D6;D6;$E$2)
waarbij geldt dat als de eerste woondag eerder ligt dan de 1e dag van de kalendermaand (D6) gebruik dan de datum in D6 en anders gebruik je de eerste woondag

 

[VenA]

Als je complexe formules wil maken dan kan je het beste dmv een aantal hulpkolommen de deelproblemen oplossen. Als elk deelprobleem werkt dan is het een kwestie van alles samenvoegen tot 1 formule.
Dat het niet perse de kortste formule oplevert maakt niet zoveel uit.

 

[Groentje19]

Dank voor jullie hulp, het heeft me een goed inzicht gegeven. Vooral de stappen die VenA me aangaf hebben goed geholpen, dank!

Ondertussen heb ik een eerste ruwe versie, let niet op layout en kleurgebruik, da's meer voor mezelf even om alles uit elkaar te houden. Aan layout en kleuren en taalgebruik ga ik later werken . Ook moet er nog data gekoppeld worden en wil ik de kalenderjaar overlopende posten gaan berekenen. Ik heb nu 10 werkbladen waarvan een deel data. Ik ga nog even bedenken of ik het nog wat efficiënter kan inrichten.

Deze werkmap is gebaseerd op centrale meters, hierna wil ik hem nog verder uitbreiden met mogelijkheid voor tussenmeters in te voeren zodat het werkelijk gebruik berekend kan worden.

Adresselectie doe ik middels een dropdown, waarna alle info en data van deze woning worden opgehaald.

Ik worstel nog wel een beetje met kolombreedtes als ik op verschillende pagina's met verschillende breedten werk (bijv. de eerste pagina en de specificatiepagina's). Ook qua kleurgebruik met formules en invoervelden etc.

Hoe doen jullie dat?

Tips zijn altijd welkom!