Home > Datums berekenen
|
Vlot leren rekenen met datumsIk kom graag een training Excel op uw bedrijf geven. Ook over talloze andere interessante rekenfuncties. In overleg spreken we af wat u gaat leren. Dit kan met beginners en gevorderden, en kan gaan over Excel 2007, 2010, 2013, 2016 of 2019.
Uw project plannen met Excel
U kunt uw project plannen met een kant-en-klaar schema.
* Vul de diverse onderdelen in * Vul in, hoeveel werkdagen ieder onderdeel duurt Excel laat de dagen volgens de planning in gekleurde balken zien. Vrije dagen, weekends en feestdagen worden automatisch overgeslagen. Lees meer over het schema Projectplanning in Excel. |
Uitgebreid lezen over datums
Ze worden met afbeeldingen verhelderd.
Klik op het boek om het in te kijken en te bestellen. Daarin leest u bijvoorbeeld ook hoe u een projectplanning als een Gantt-grafiek weergeeft. |
Op welke dag valt Koningsdag?
Sinds 2014 wordt koningsdag gevierd op 27 april. Als dat op zondag valt, verschuift het naar zaterdag 26 april.
U berekent koningsdag in Excel met de formule
=ALS(WEEKDAG(DATUM(C2;4;27))=1;DATUM(C2;4;26);DATUM(C2;4;27))
Hier staat het jaartal in cel C2
De formule houdt in: als de weekdag van 27 april een 1 is (d.w.z.: op zondag valt), neem dan 26 april; geef in de andere gevallen 27 april.
Het kan ook met een kortere formule:
=DATUM(C2;4;27)-ALS(WEEKDAG(DATUM(C2;4;27))=1;1;0)
Deze houdt in: neem de datum 27 april en als de weekdag van 27 april een zondag is, trek er dan 1 van af.
U berekent koningsdag in Excel met de formule
=ALS(WEEKDAG(DATUM(C2;4;27))=1;DATUM(C2;4;26);DATUM(C2;4;27))
Hier staat het jaartal in cel C2
De formule houdt in: als de weekdag van 27 april een 1 is (d.w.z.: op zondag valt), neem dan 26 april; geef in de andere gevallen 27 april.
Het kan ook met een kortere formule:
=DATUM(C2;4;27)-ALS(WEEKDAG(DATUM(C2;4;27))=1;1;0)
Deze houdt in: neem de datum 27 april en als de weekdag van 27 april een zondag is, trek er dan 1 van af.
Koningsdag of koninginnedag?
In de jaren vóór 2014 viel koninginnedag op 30 april. Ook daarbij gold de regel: viel het op zondag, dan werd het een dag eerder. Als het jaartal in C2 staat, is uw formule
=ALS(C2>2013;DATUM(C2;4;27)-ALS(WEEKDAG(DATUM(C2;4;27))=1;1;0);DATUM(C2;4;30)-ALS(WEEKDAG(DATUM(C2;4;30))=1;1;0))
Die betekent: als het jaartal groter is dan 2013, neem dan 27 april minus als de weekdag van 27 april een 1 is (op zondag valt) één dag en anders nul dagen; en als het jaartal niet hoger is dan 2013, neem dan 29 april minus als de weekdag van 30 april een 1 is (op zondag valt) één dag en anders nul dagen.
Deze kan ook compacter:
=DATUM(C2;4;ALS(C2>2013;27;30))-ALS(WEEKDAG(DATUM(C2;4;ALS(C2>2013;27;30)))=1;1;0)
Hier staat: geef de datum van april in dat jaar met als dag na 2013: 27 en anders 30, minus als de weekdag van (na 2013 op 27 en anders op 30) april 1 is (dus op zondag valt) één dag en anders nul dagen.
=ALS(C2>2013;DATUM(C2;4;27)-ALS(WEEKDAG(DATUM(C2;4;27))=1;1;0);DATUM(C2;4;30)-ALS(WEEKDAG(DATUM(C2;4;30))=1;1;0))
Die betekent: als het jaartal groter is dan 2013, neem dan 27 april minus als de weekdag van 27 april een 1 is (op zondag valt) één dag en anders nul dagen; en als het jaartal niet hoger is dan 2013, neem dan 29 april minus als de weekdag van 30 april een 1 is (op zondag valt) één dag en anders nul dagen.
Deze kan ook compacter:
=DATUM(C2;4;ALS(C2>2013;27;30))-ALS(WEEKDAG(DATUM(C2;4;ALS(C2>2013;27;30)))=1;1;0)
Hier staat: geef de datum van april in dat jaar met als dag na 2013: 27 en anders 30, minus als de weekdag van (na 2013 op 27 en anders op 30) april 1 is (dus op zondag valt) één dag en anders nul dagen.
Hoe Excel rekent met datumsVoor het rekenen met datums kent Excel aan één dag de waarde 1 toe. Het programma telt de dagen vanaf 1 januari 1900. Bijvoorbeeld 1 januari 2000 heeft het getal 36526, want het was toen 100 jaar en 25 schrikkeldagen later.
Dat u zo'n getal als een datum ziet, komt alleen door de opmaak. Een cel krijgt automatisch de datum-opmaak als u een datum invoert als 7-3-2020 of als 7/3/2020. Tip 1: u voert de datum van vandaag in door te drukken op Ctrl+; (puntkomma) gevolgd door Enter. Tip 2: valt de datum in het huidige jaar, dan hoeft u het jaartal er niet bij te typen, dat voegt Excel eraan toe; typ bijvoorbeeld 7-3 en u krijgt automatisch 7-3-2020 in het betreffende jaar (kijk hiervoor in de formulebalk). Aantal dagen tussen twee datumsWilt u het verschil tussen twee datums weten, dan trekt u ze van elkaar af. Typ bijvoorbeeld in A1 de ene datum en in B2 de andere datum (B2 moet later vallen dan A2).
Zet ernaast de formule =B2-A2 U ziet het aantal dagen dat ertussen ligt. |
Zelf leren werken met Excel?Ik kom graag op uw bedrijf een training Excel geven. Over datums en over talloze andere interessante rekenfuncties. In overleg spreken we af wat u gaat leren. Dit kan met beginners en gevorderden, en kan gaan over elke versie van Excel.
Feestdagen in een willekeurig jaarGeef een jaar op en Excel laat de feestdagen zien.
Download dit kant-en-klare werkblad met feestdagen. AOW-datumVul je geboortedatum in en Excel laat zien wanneer je AOW begint.
Download gratis dit kant-en-klare werkblad. |
Formules voor datums
In de volgende voorbeelden wordt de datum steeds opgeven in de formule zelf, tussen dubbele aanhalingstekens.
Maar de datum kan ook in een cel staan, dan verwijst u in de formule naar die cel.
Maar de datum kan ook in een cel staan, dan verwijst u in de formule naar die cel.
Functie in een voorbeeld:
=DAG("1-7-2020") =DAG(B3) =MAAND("1-7-2020") =JAAR("1-7-2020") =("1-7-2020"-"1-1-2020") =DAGEN360("1-1-2020";"1-7-2020") =DATUMVERSCHIL("1-1-2020";"1-7-2020";"d") =DATUMVERSCHIL("1-1-2018";"1-7-2020";"m") =DATUMVERSCHIL("1-1-2016";"1-7-2020";"y") =DATUMVERSCHIL("1-1-2016";"1-2-2020";"yd") =DATUMVERSCHIL("1-1-2016";"1-7-2020";"ym") =DATUMVERSCHIL("1-1-2016";"14-7-2020";"md") |
Toelichting:
Geef uit een datum de dag van de maand Geef de dag van de datum in B3 Geef uit een datum de maand Geef uit een datum het jaartal Aantal dagen tussen twee datums Aantal dagen tussen twee datums met een jaar van 360 dagen Aantal dagen tussen de datums Aantal hele maanden tussen de datums Aantal hele jaren tussen de datums Aantal dagen, zonder de jaren Aantal maanden, zonder de jaren Aantal dagen, zonder de jaren en maanden |
Uitkomst:
1 bijv. 2 7 2020 181 180 182 30 4 31 6 13 |
=DATUM(2020;3;7)
=DATUMWAARDE("7-3-2020") =JAAR.DEEL("1-1-2020";"1-7-2020") =LAATSTE.DAG("5-7-2020";3) =NETTO.WERKDAGEN("1-1-2020";"31-12-2020") =WEEKDAG("1-8-2020") =WEEKNUMMER("1-7-2020") =WERKDAG("1-7-2020";7) =ZELFDE.DAG("1-7-2020";12) =ZELFDE.DAG("1-7-2020";12,5*12) |
Stel datum samen uit: jaar, maand, dag
Geef serieel getal van een datum Geef het deel van een jaar tussen de datums, als een getal tussen 0 en 1 Tel x maanden verder en geef de laatste dag van die maand Geef het aantal hele werkdagen tussen twee datums Geef uit een datum de dag van de week: van 1=zondag tot en met 7=zaterdag Geef het weeknummer van een datum * volgens Amerikaanse telling! Zie voor Europese telling hieronder Geef vanaf een datum x aantal werkdagen later Geef een datum, een x aantal maanden na de opgegeven datum Bijv. 12,5 jaar later geeft u op als 12,5 maal 12 maanden |
07-03-2020
43.987 0,5 31-10-2020 262 7 27 10-7-2020 1-7-2021 1-1-2033 |
Formules met VANDAAG
In deze voorbeelden is het 'vandaag' 15 mei 2020
Functie in een voorbeeld: =VANDAAG() =ZELFDE.DAG(VANDAAG();1) =LAATSTE.DAG(VANDAAG();0) =LAATSTE.DAG(VANDAAG();1) =GEHEEL((MAAND(VANDAAG())+2)/3) =REST(GEHEEL((MAAND(VANDAAG())+2)/3);4)+1 met REST komt na kwartaal 4 weer kwartaal 1 |
Toelichting: Geef de datum van vandaag Vandaag over een maand Einde van de huidige maand Einde van de volgende maand Huidige kwartaal Volgende kwartaal |
Uitkomst: 15-05-2020 15-06-2020 31-05-2020 30-06-2020 2 3 |
Leeftijd berekenen
U kunt met Excel iemands leeftijd berekenen als u de geboortedatum weet. Dat kan op twee manieren.
Hoe oud wordt iemand ergens in dit jaar?
U trekt hiervoor het geboortejaar af van het huidige jaar. Staat de geboortedatum in cel A2, dan neemt u =JAAR(VANDAAG())-JAAR(A2) Hierbij maakt het niet uit wanneer in het jaar de verjaardag valt. Hoe oud is iemand op een bepaalde dag? U wilt de leeftijd weten op een bepaalde peildatum, bijvoorbeeld: hoe oud is iemand op 15-9-2018? Typ dan de datum 15-9-2018 eenmalig in bijvoorbeeld cel C1. Staat de eerste datum in A2, dan is uw formule =GEHEEL((C$1-A2)/365,25) Zie voor de toelichting hiernaast. Staan er meer datums in kolom A, dan kunt u formule omlaag kopiëren. Doordat er een $ in C$1 staat, zullen alle kopieën verwijzen naar de cel met deze datum. Wilt u de leeftijd weten op een andere peildatum, dan hoeft u alleen de datum in cel C1 maar te veranderen. Als alternatief kunt u gebruiken: =DATUMVERSCHIL(A2;C$1;"y") |
Hoe oud is iemand vandaag?
Het ligt voor de hand om de formule te nemen (als de geboortedatum in cel A2 staat): =(VANDAAG()-A2)/365,25 Het verschil tussen vandaag en A2 in dagen, gedeeld door 365,25 (het aantal dagen per jaar plus gemiddeld een schrikkeldag eens per vier jaar). Maar de uitkomst is een getal met decimalen, die worden afgerond op het dichtstbij zijnde hele getal (u maakt de decimalen zichtbaar met de knop Meer decimalen). Dat geeft niet altijd de goede uitkomst. U moet alleen het hele getal uit de uitkomst hebben. Dat krijgt u met de formule =GEHEEL((VANDAAG()-A2)/365,25) Ook hier staat de datum in cel A2. Het verschil wordt gedeeld door 365,25 en naar beneden afgerond. Alternatief: U kunt de leeftijd op dit moment ook berekenen met =DATUMVERSCHIL(A2;VANDAAG();"y") |
Op welke datum valt deze verjaardag? |
Welke dag van de week is die datum? |
Iemand is geboren op 17-5-78 en u wilt weten op welke dag van de week de verjaardag dit jaar valt.
Typ, bijvoorbeeld in cel B2, de datum 17-5-1978. Om 17-5 van dit jaar te krijgen, hebt u de volgende onderdelen nodig: * JAAR(VANDAAG()) geeft het jaartal van vandaag; dit mag ook met JAAR(NU()) * MAAND(B2) geeft de maand van B2 * DAG(B2) geeft de dag van B2. Met deze onderdelen geeft de functie DATUM de datum uit B2 in het huidige jaar; de formule is =DATUM(JAAR(VANDAAG());MAAND(B2);DAG(B2)) |
Wilt u van een datum zien op welke dag van de week die valt, dan regelt u dat met de Opmaak.
Klik met de rechtermuisknop op de cel met de datum; er gaat een menu open. Kies Celeigenschappen (of druk op Ctrl+1). Klik op Aangepast (onder in het venster) en typ in het vak onder Type de code dddd d mmmm jjjj Of open in het tabblad Start het opmaakmenu boven in de groep Getal en klik in dat menu op Lange datumnotatie. De datum 16-7-1985 ziet er dan uit als: dinsdag 16 juli 1985 Of gebruik de functie WEEKDAG. Met =WEEKDAG(B2) ziet u een cijfer 1 t/m 7, voor zondag t/m zaterdag. |
Jubileum voorspellen
Typt u een datum in bijvoorbeeld cel A2, dan vindt u de datum 25 jaar later met de volgende formule:
=DATUM(JAAR(A2)+25;MAAND(A2);DAG(A2)) In Excel 2007, 2010 en 2013 kan dit ook met =ZELFDE.DAG(A2;25*12) |
Wilt u weten wanneer het 12,5 jaar na de datum in A2 is, dan neemt u
=DATUM(JAAR(A2);MAAND(A2)+12,5*12;DAG(A2)) In Excel 2007, 2010 en 2013 kan dit ook met =ZELFDE.DAG(A2;12,5*12) |
Wanneer zijn wij samen 100 jaar?
Ook een mooie mijlpaal. Dat berekent u als volgt.
Typ de geboortedatum van de een in cel D2, en die van de ander in E2. Typ in G2 de formule =GEMIDDELDE(D2:E2) U krijgt dan het gemiddelde van de beide geboortedata. Typ in cel D5 het getal 100 (als u wilt weten wanneer u samen 100 jaar wordt). Typ in cel E5 de formule =G2+D5/2*365,25 Maak de cellen met datums op als Datum en u ziet wanneer u de mijlpaal bereikt. |
Het kan ook korter
U kunt de stap van de formule in cel G2 ook overslaan. Typ wel de geboortedatums in cel D2 en in E2. En typ in cel D5 het getal 100 (als u wilt weten wanneer u samen 100 jaar wordt). Typ in cel E5 de formule =GEMIDDELDE(D2:E2)+D5/2*365,25 Als u wilt weten wanneer u samen 50 wordt, hoeft u alleen het getal in cel D5 aan te passen. |
De formules hieronder kunt u ook in een gratis bestand downloaden.
Datum van Pasen berekenen
De christelijke feesten in het voorjaar hangen samen met de datum van Pasen. Pasen valt op de eerste zondag na de eerste volle maan na het begin van de lente (21 maart).
Tot het jaar 2078 klopt deze formule: Deze formule klopt tot 2368: |
Typ een jaartal in cel C3 (tussen 1900 en 9999).
Met de volgende formule berekent Excel de datum van Eerste Paasdag voor elk willekeurig jaar. Het jaartal staat in C3. =VAST(("4/"&C3)/7+REST(REST(C3;19)*19-7;30)*14%;0)*7-6 =AFRONDEN.BENEDEN(DATUM(C3;5;DAG(MINUUT(C3/38)/2+56));7)-34 Plaats deze formule in cel C8. De volgende feesten worden afgeleid van de Paasdatum en verwijzen daarom steeds naar deze C8. |
Feestdagen berekenen
Carnaval
Aswoensdag Goede Vrijdag Tweede Paasdag Hemelvaartsdag Eerste Pinksterdag Tweede Pinksterdag Kerst berekent u met de functie DATUM Eerste Kerstdag Tweede Kerstdag |
=C8-49
=C8-46 =C8-2 =C8+1 =C8+39 =C8+49 =C8+50 =DATUM(C3;12;25) =DATUM(C3;12;26) |
in cel C5
in cel C6 in cel C7 in cel C9 in cel C10 in cel C11 in cel C12 in cel C13 in cel C14 |
Andere bijzondere datums
Begin van zomertijd - laatste zondag in maart Begin van wintertijd - laatste zondag in oktober Moederdag - tweede zondag in mei Vaderdag - derde zondag in juni Prinsjesdag - derde dinsdag in september Het Chinees Nieuwjaar berekent u met |
In deze voorbeelden staat het jaartal in cel C3.
=DATUM(C3;3;31)-WEEKDAG(DATUM(C3;3;31))+1 =DATUM(C3;10;31)-WEEKDAG(DATUM(C3;10;31))+1 =DATUM(C3;5;14)-WEEKDAG(DATUM(C3;5;14))+1 =DATUM(C3;6;21)-WEEKDAG(DATUM(C3;6;21))+1 =DATUM(C3;9;19)-WEEKDAG(DATUM(C3;9;19))+3 =GEHEEL(DATUM(C3-1;12;21)+61,6-REST(DATUM(C3-1;12;21);(19*354+11*355)/360))+ALS(EN(REST(REST(C3;29,53);19)>8,6;REST(REST(C3;29,53);19)<10,6);1;0) |
Islamitische feesten:
Begin van de Ramadan Suikerfeest - 30 dagen later Offerfeest - 14 weken / 98 dagen later Joodse feesten: Rosj haSjana - Nieuwjaar MAAR: Rosj haSjana valt nooit op een woensdag, vrijdag of zondag, dus moet de volgende aanvulling erbij: Yom Kippoer - Grote verzoendag Soekot - Loofhuttenfeest Simchat Thora - Vreugde der Wet |
Typ een jaartal in cel C3 (tussen 1900 en 9999) en zet in cel C25:
=GEHEEL((C3-1900)*19*354+11*355)/360*12+1421,44) =C25+30 (in cel C26) =C25+98 (in cel C27) Het jaartal staat in C3 de volgende formule komt in cel B30: =DATUM(C3;8;31)+6,05+1,554*REST(12*(REST(C3;19)+1);19)+0,25*REST(C3-1900;4)-0,003*(C3-1900) Deze moet ernaast, in cel C30: =B30+ALS(OF(WEEKDAG(B30)=1;WEEKDAG(B30)=4;WEEKDAG(B30)=6;EN(WEEKDAG(B30)=2;REST(12*(REST(C32;19)+1);19)>11));1;ALS(EN(WEEKDAG(P71)=3;REST(12*(REST(A2;19)+1);19)<12);2;0)) =C30+9 (in cel C31) =C30+14 (in cel C32 =C30+22 (in cel C33) |
Formule voor Europees weeknummer
Als u het weeknummer van een datum berekent met de functie WEEKNUMMER, klopt de uitkomst niet altijd. Dat komt doordat Excel voor deze functie het Amerikaanse systeem gebruikt. In de VS is de week met 1 januari altijd week 1 en begint week 2 op de zondag erna. Daardoor heeft week 1 meestal geen zeven dagen. Bijvoorbeeld in week 1 van 2010 zitten alleen 1 en 2 januari.
|
Maar de Europese telling werkt volgens ISO. In Europa geldt een weeknummer altijd voor zeven dagen. De week met de eerste donderdag van het jaar is week 1.
Daardoor krijgt u in sommige jaren een verschil. |
U krijgt het weeknummer volgens de Europese telling met de formule:
=(A1-WEEKDAG(A1-1)+4-(GEHEEL(DATUM(JAAR(A1-WEEKDAG(A1-1)+4);1;2)/7)*7-2))/7
In dit voorbeeld staat de datum in cel A1.
=(A1-WEEKDAG(A1-1)+4-(GEHEEL(DATUM(JAAR(A1-WEEKDAG(A1-1)+4);1;2)/7)*7-2))/7
In dit voorbeeld staat de datum in cel A1.
Functie voor Europees weeknummer vanaf Excel 2013
In Excel 2013 is de functie ISO.WEEKNUMMER geïntroduceerd en is dit gedoe voorbij.
Als de datum in A1 staat, is uw formule
=ISO.WEEKNUMMER(A1)
U krijgt het weeknummer van deze datum volgens het ISO-systeem.
* Deze functie is nieuw vanaf Excel 2013 en werkt dus niet in oudere versies.
Als de datum in A1 staat, is uw formule
=ISO.WEEKNUMMER(A1)
U krijgt het weeknummer van deze datum volgens het ISO-systeem.
* Deze functie is nieuw vanaf Excel 2013 en werkt dus niet in oudere versies.
Help! Ik zie geen datum, maar een groot getal
Zoals bovenaan uitgelegd, verwerkt Excel een datum als een getal. Alleen door de opmaak ziet u dat als een datum.
* Ziet u onverwacht een groot getal rond de 40.000 en wilt u daar een datum zien, maak die cel dan op als Datum. * Misschien hebt u per ongeluk op de toetsen Ctrl+T gedrukt. Daardoor ziet u namelijk alle formules in het werkblad in plaats van hun uitkomsten. En datums veranderen dan in hun getalswaarde. Druk nogmaals op Ctrl+T en u ziet de getallen weer als datums. |
Dit houdt dus ook in: wilt u weten welke getalswaarde achter een datum in uw werkblad zit, klik dan in de tab Start op de keuzelijst Getalnotatie (in de groep Getal); er gaat een keuzelijst open. Kijk daarin bij Getal en u ziet de achterliggende getalswaarde van die datum.
|