Excel - dagen per maand in een periode

[evdgriek]

Mijn vraag

Ik heb een periode bv van 2-02-2022 t/m 14-4-2022

Hoe bepaal ik hoeveel dagen er in elke tussenliggende maand liggen dus:
Februari 27
Maart 31
April 12

...

Relevante software en hardware die ik gebruik
Excel

Wat ik al gevonden of geprobeerd heb
Ik heb diverse als en functies bepaald maar blijf uitzonderingen houden

[99ruud99]

Het verschil tussen twee datums berekenen

[GRDavies75]

evdgriek schreef op maandag 18 juli 2022 @ 11:38:
Mijn vraag

Ik heb een periode bv van 2-02-2022 t/m 14-4-2022

Hoe bepaal ik hoeveel dagen er in elke tussenliggende maand liggen dus:
Februari 27
Maart 31
April 12

...

Relevante software en hardware die ik gebruik
Excel

Wat ik al gevonden of geprobeerd heb
Ik heb diverse als en functies bepaald maar blijf uitzonderingen houden

toon volledige bericht

Veel zaken zijn in Excel 'gewoon' een nummer (maar in dit geval in het datum-formaat).
Je kan dus 'gewoon' de datum's van elkaar aftrekken, een 1 staat voor 1 dag.

Note: volgens mij maak je een denkfout in je voorbeeld, moet je niet 14 dagen in April hebben (dat of je moet in maart ook consistent zijn en 29 aanhouden).?

[ Voor 65% gewijzigd door GRDavies75 op 18-07-2022 11:47 . Reden: extra info ]

[evdgriek]

Bedankt voor de snelle reactie,

Het klopt wat jullie zeggen maar als ik twee data heb bv 2-2 en 14-4 wil ik niet deze data van elkaar aftrekken maar bepalen dat er dan 27 dagen in februari vallen, 31 in maart en 14 in april.

Nogmaals dank

[RHE218]

evdgriek schreef op maandag 18 juli 2022 @ 11:48:
Bedankt voor de snelle reactie,

Het klopt wat jullie zeggen maar als ik twee data heb bv 2-2 en 14-4 wil ik niet deze data van elkaar aftrekken maar bepalen dat er dan 27 dagen in februari vallen, 31 in maart en 14 in april.

Nogmaals dank

Ik denk dat de/een formule hier ingewikkelder is dan zelf even kijken wat de afwijking is in de eerste en laatste maand... alle tussenliggende maanden zullen niet afwijken van het gebruikelijke aantal dagen

[evdgriek]

Hoi NHE,

Dat klopt maar helaas niet als je 600 regels hebt die ook nog maandelijks muteren. Mijn concentratie laat het dan wel eens afweten helaas .

[F_J_K]

De vraag is inderdaad wat complexer. Leuk
Mijn idee, niet uitgewerkt, kan ik misschien later proberen:
kolom A: 1 jan 2022, etc
kolom B: laatste dag van maand, bijv. =LAATSTE.DAG(A2;0)
kolom C: als datumvan > B2 of datumtot < A2 dan 0 anders 1 [dus: is de maand relevant?]
kolom D: als datumvan <A2 en datumtot > B2 dan B2-A2+1 anders .. [is het een tussenmaand]

en dan nog een test inbouwen voor de .., B2-datum_van+1 als het aan het begin van de periode is, datumtot-A2+1 als aan het einde. Resultaat is dan C2*D2 (want moet nul zijn voor de maanden buiten de periode om)

[tritimee]

paar kolommen achter je data zetten met daarin de eerste van de maand en per kolom uitrekenen


Max(Kolomdatum (1-feb), Startdatum periode) - Min (EOMONTH(Kolomdatum;0); Einddatum periode)

en dat per kolom er in zetten?

Edit: ook nog even een if statement als de maand niet relevant is voor je periode

[ Voor 15% gewijzigd door tritimee op 18-07-2022 12:24 ]

[F_J_K]

=C2*MAX(ALS(EN(datum_van<A2;datum_tot>B2);B2-A2+1;ALS(C1=0;B2-datum_van+1;datum_tot-A2+1))) werkt in mijn opzetje

Kan inderdaad nog wat compacter door EOMONTH / LAATSTEDAG in te bouwen in de formule. Kolommen B en C zijn niet per se nodig, het kan in 1 lange formule. Maar een paar hulpkolommen is vaak wel zo handig.

[evdgriek]

@F_J_K Hier mijn formule ;-)

[Djordjo]

Je hebt 6 verschillende situaties; de begin- en einddatum kunnen vóór, in of ná de betreffende maand liggen.

Het beste kun je eerst de situaties eruit filteren waarbij er geen dagen zijn; de begindatum is na laatste dag van de maand of de einddatum is voor de eerste dag van de maand.
Daarna neem je het maximum van de begindatum en eerste dag maand en het minimum van de einddatum en laatste dag maand, en bereken je het verschil (plus 1).

[ Voor 4% gewijzigd door Djordjo op 18-07-2022 14:37 ]

[F_J_K]

Gaat nog niet helemaal goed Zo te zien alleen nog de check bij de laatste maand voor start an de reeks.
Edit: dus waar de start van de reeks is na kolom B.

[ Voor 20% gewijzigd door F_J_K op 18-07-2022 14:30 ]

[evdgriek]

thanx, iemand een idee voor de formule?

[F_J_K]

Ik was m’n post aan het aanvullen Zie mijn eerdere post, waar je alleen rij en kolom wilt aanpassen.

Wat is je huidige formule?

[evdgriek]

dit is mijn formule, ik krijg alleen 2 data in dezelfde maand niet voor elkaar

[F_J_K]

Ah ik zag de formulebalk niet De formule tekstueel plaatsen was makkelijker, dan ook als test te gebruiken door anderen. Overigens, welkom

[ Voor 8% gewijzigd door F_J_K op 18-07-2022 17:00 ]

[Reptile209]

evdgriek schreef op maandag 18 juli 2022 @ 14:41:
[...], ik krijg alleen 2 data in dezelfde maand niet voor elkaar [...]

Dan kan je in de maand-berekeningen nog iets toevoegen voor het geval dat MAAND(A5) = MAAND(B5) als 'special case', namelijk B5-A5+1.

Overigens: is het nog relevant dat er periodes over de jaarwisseling kunnen lopen, of maakt dat niet uit?

[F_J_K]

De C2 - A8 +1 is de verkeerde, je wilt B8 - A8 + 1 denk ik.

Reptile209 schreef op maandag 18 juli 2022 @ 16:28:
[...]

Dan kan je in de maand-berekeningen nog iets toevoegen voor het geval dat MAAND(A5) = MAAND(B5) als 'special case', namelijk B5-A5+1.

Overigens: is het nog relevant dat er periodes over de jaarwisseling kunnen lopen, of maakt dat niet uit?

Dat laatste gaat volgens mij wel goed. 1 jan 2023 > 31 dec 2022.

[Teun_2]

Indien nodig RIJ() te vervangen door kolom om op een rij door te trekken.

Of als je enkel het totaal van alle maanden opgeteld hebt: =DAG(Begin)+DAG(LAATSTE.DAG(Eind;0))-DAG(Eind)+Eind-Begin

Als je met je lange als-formule verder wil (die enkel werkt voor de kolommen die je definieert, dus niet blijft werken over jaren heen), moet je zeker eens kijken naar de ALS.VOORWAARDEN ALS.VOORWAARDEN, functie

[ Voor 30% gewijzigd door Teun_2 op 18-07-2022 18:45 ]

[Djordjo]

evdgriek schreef op maandag 18 juli 2022 @ 14:30:
thanx, iemand een idee voor de formule?

Jazeker:

Djordjo schreef op maandag 18 juli 2022 @ 14:28:
Het beste kun je eerst de situaties eruit filteren waarbij er geen dagen zijn; de begindatum is na laatste dag van de maand of de einddatum is voor de eerste dag van de maand.
Daarna neem je het maximum van de begindatum en eerste dag maand en het minimum van de einddatum en laatste dag maand, en bereken je het verschil (plus 1).

Oftewel:

code:

=ALS(OF($A8>C$2;$B8<C$1);0;MIN($B8;C$2)-MAX($A8;C$1)+1)

Da's alles!

[Reptile209]

Djordjo schreef op maandag 18 juli 2022 @ 22:25:
[...]

Jazeker:

[...]

Oftewel:

code:

=ALS(OF($A8>C$2;$B8<C$1);0;MIN($B8;C$2)-MAX($A8;C$1)+1)

Da's alles!

Maar TS wil weten hoeveel dagen er in elke maand tussen begin en einddatum zitten, niet het totale aantal dagen.

[Djordjo]

Reptile209 schreef op maandag 18 juli 2022 @ 22:54:
Maar TS wil weten hoeveel dagen er in elke maand tussen begin en einddatum zitten, niet het totale aantal dagen.

Ja dat snap ik TS kwam met de volgende spreadsheet:

evdgriek schreef op maandag 18 juli 2022 @ 14:41:
dit is mijn formule, ik krijg alleen 2 data in dezelfde maand niet voor elkaar[Afbeelding]

Die formule voor de maand januari heb ik dus gecorrigeerd. Vervolgens slechts een kwestie van kopiëren naar de andere maanden....

[evdgriek]

super bedankt allemaal, ik kan nu in ieder geval verder

[PageFault]

RHE218 schreef op maandag 18 juli 2022 @ 12:11:
[...]


Ik denk dat de/een formule hier ingewikkelder is dan zelf even kijken wat de afwijking is in de eerste en laatste maand... alle tussenliggende maanden zullen niet afwijken van het gebruikelijke aantal dagen

Behalve februari Ik zou de eerder genoemde DATEDIF gebruiken.

[izamani]

Ik heb het volgende gedaan:
A1 "Begin"; B1 "Eind"; C1 "1-1-2022"; D1 "1-2-2022: ; etc (Maanden in eigenschappen veranderd naar "mmm")
A2 "Datum"; B2 "Datum"; C2 "=SOMPRODUCT(--(MAAND(RIJ(INDIRECT($A2 & ":" & ALS($B2="";VANDAAG();$B2))))=MAAND(C$1)))" ; D2 =SOMPRODUCT(--(MAAND(RIJ(INDIRECT($A2 & ":" & ALS($B2="";VANDAAG();$B2))))=MAAND(D$1))) ; etc

Probleem wat evdgriek vermeld krijg ik hier niet over 2 datums in zelfde maand.