Excel Handleiding: Datum- en Tijdcalculatie (verschil berekenen, leeftijd, optellen/aftrekken)
In Excel worden datum- en tijdsberekeningen vaak gebruikt, zoals het berekenen van het verschil tussen twee datums/tijden, het optellen of aftrekken van datum-tijdwaarden, het bepalen van de leeftijd op basis van een geboortedatum, enzovoort. In deze handleiding vind je vrijwel alle scenario's rondom datum- en tijdsberekeningen, inclusief de bijbehorende methoden.
In deze handleiding maak ik enkele voorbeelden om de methoden uit te leggen. Je kunt de verwijzingen aanpassen naar jouw situatie wanneer je onderstaande VBA-code of formules gebruikt.
1. Verschil berekenen tussen twee datums/tijden
Het berekenen van het verschil tussen twee datums of twee tijdstippen is waarschijnlijk het meest voorkomende probleem bij datum- en tijdsberekeningen in Excel. De onderstaande voorbeelden kunnen je helpen om efficiënter te werken wanneer je met soortgelijke situaties te maken krijgt.
1.11 Bereken het verschil tussen twee datums in dagen/maanden/jaren/weken
Met de Excel-functie DATEDIF kun je snel het verschil tussen twee datums berekenen in dagen, maanden, jaren en weken.
Klik voor meer informatie over de DATEDIF-functie
Verschil in dagen tussen twee datums
Om het verschil in dagen tussen twee datums in cel A2 en B2 te berekenen, gebruik je de volgende formule:
=DATEDIF(A2,B2,"d")
Druk op Enter om het resultaat te krijgen.
Verschil in maanden tussen twee datums
Om het verschil in maanden tussen twee datums in cel A5 en B5 te berekenen, gebruik je de volgende formule:
=DATEDIF(A5,B5,"m")
Druk op Enter om het resultaat te krijgen.
Verschil in jaren tussen twee datums
Om het verschil in jaren tussen twee datums in cel A8 en B8 te berekenen, gebruik je de volgende formule:
=DATEDIF(A8,B8,"y")
Druk op Enter om het resultaat te krijgen.
Verschil in weken tussen twee datums
Om het verschil in weken tussen twee datums in cel A11 en B11 te berekenen, gebruik je de volgende formule:
=DATEDIF(A11,B11,"d")/7
Druk op Enter om het resultaat te krijgen.
Opmerking:
1) Wanneer je bovenstaande formule gebruikt om het verschil in weken te berekenen, kan het resultaat in datumformaat worden weergegeven. Je dient het resultaat dan om te zetten naar algemeen of getal, afhankelijk van je behoefte.
2) Wanneer je bovenstaande formule gebruikt om het verschil in weken te berekenen, kan het resultaat een decimaal getal zijn. Als je het aantal volledige weken wilt krijgen, kun je de functie ROUNDDOWN toevoegen, zoals hieronder getoond, om het aantal gehele weken te berekenen:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Bereken het aantal maanden tussen twee datums, waarbij jaren en dagen worden genegeerd
Als je alleen het verschil in maanden wilt berekenen, waarbij jaren en dagen worden genegeerd, zoals in de onderstaande schermafbeelding, kun je deze formule gebruiken.
=DATEDIF(A2,B2,"ym")
Druk op Enter om het resultaat te krijgen.
A2 is de begindatum en B2 is de einddatum.
1.13 Bereken het aantal dagen tussen twee datums, waarbij jaren en maanden worden genegeerd
Als je alleen het verschil in dagen wilt berekenen, waarbij jaren en maanden worden genegeerd, zoals in de onderstaande schermafbeelding, kun je deze formule gebruiken.
=DATEDIF(A5,B5,"md")
Druk op Enter om het resultaat te krijgen.
A5 is de begindatum en B5 is de einddatum.
1.14 Bereken het verschil tussen twee datums en geef jaren, maanden en dagen terug
Als je het verschil tussen twee datums wilt berekenen en het resultaat wilt weergeven als xx jaren, xx maanden en xx dagen, zoals in de onderstaande schermafbeelding, kun je deze formule gebruiken.
=DATEDIF(A8, B8, "y") & " jaren, " & DATEDIF(A8, B8, "ym") & " maanden, " & DATEDIF(A8, B8, "md") & " dagen"
Druk op Enter om het resultaat te krijgen.
A8 is de begindatum en B8 is de einddatum.
1.15 Bereken het verschil tussen een datum en vandaag
Om automatisch het verschil tussen een datum en vandaag te berekenen, vervang je de einddatum in bovenstaande formules door TODAY(). Hier wordt als voorbeeld het verschil in dagen tussen een eerdere datum en vandaag berekend.
=DATEDIF(A11,TODAY(),"d")
Druk op Enter om het resultaat te krijgen.
Opmerking: als je het verschil wilt berekenen tussen een toekomstige datum en vandaag, verander dan de begindatum naar vandaag en gebruik de toekomstige datum als einddatum, zoals hieronder:
=DATEDIF(TODAY(),A14,"d")
Let op: in de DATEDIF-functie moet de begindatum kleiner zijn dan de einddatum, anders krijg je de foutwaarde #GETAL!.
1.16 Bereken het aantal werkdagen met of zonder feestdagen tussen twee datums
Soms wil je het aantal werkdagen tussen twee opgegeven datums tellen, met of zonder feestdagen.
In dit onderdeel gebruik je de functie NETWORKDAYS.INTL:
Klik op NETWORKDAYS.INTL voor uitleg over de argumenten en het gebruik.
Werkdagen tellen inclusief feestdagen
Om het aantal werkdagen inclusief feestdagen tussen twee datums in cel A2 en B2 te tellen, gebruik je deze formule:
=NETWORKDAYS.INTL(A2,B2)
Druk op Enter om het resultaat te krijgen.
Werkdagen tellen exclusief feestdagen
Om het aantal werkdagen tussen twee datums in cel A2 en B2 te tellen, waarbij de feestdagen in bereik D5:D9 worden uitgesloten, gebruik je deze formule:
=NETWORKDAYS.INTL(A5,B5,1,D5:D9)
Druk op Enter om het resultaat te krijgen.
Opmerking:
In bovenstaande formules worden zaterdag en zondag als weekend beschouwd. Als je andere weekenddagen hebt, pas dan het argument [weekend] aan naar wens.
1.17 Bereken het aantal weekenden tussen twee datums
Als je het aantal weekenden tussen twee datums wilt tellen, kunnen de functies SOMPRODUCT of SOM je hierbij helpen.
Om het aantal weekenden (zaterdag en zondag) tussen twee datums in cel A12 en B12 te tellen:
=SOMPRODUCT(--(WEEKDAG(RIJ(INDEIRECT(A12&":"&B12)),2)>5))
Of
=SOM(HELE(GETAL((WEEKDAG(A12-{1,7})+B12-A12)/7)))
Druk op Enter om het resultaat te krijgen.
1.18 Bereken het aantal specifieke weekdagen tussen twee datums
Om het aantal van een specifieke weekdag, zoals maandag, tussen twee datums te tellen, kun je de combinatie van de functies HELE en WEEKDAG gebruiken.
Cel A15 en B15 zijn de twee datums waarvoor je het aantal maandagen wilt tellen. Gebruik hiervoor deze formule:
=HELE((WEEKDAG(A15-2)-A15 +B15)/7)
Druk op Enter om het resultaat te krijgen.
Wijzig het getal van de weekdag in de WEEKDAG-functie om een andere weekdag te tellen:
1 is Zondag,2 is Maandag,3 is Dinsdag,4 is Woensdag,5 is Donderdag,6 is Vrijdag en7 is Zaterdag
1.19 Bereken het aantal resterende dagen in maand/jaar
Soms wil je weten hoeveel dagen er nog over zijn in de maand of het jaar op basis van een opgegeven datum, zoals in de onderstaande schermafbeelding:
Resterende dagen in de huidige maand berekenen
Klik op EOMONTH voor uitleg over het argument en het gebruik.
Om het aantal resterende dagen van de huidige maand in cel A2 te berekenen, gebruik je deze formule:
=EOMONTH(A2,0)-A2
Druk op Enter en sleep de vulgreep om deze formule toe te passen op andere cellen indien nodig.
Tip: de resultaten kunnen als datumformaat worden weergegeven. Wijzig ze indien nodig naar algemeen of getal.
Resterende dagen in het huidige jaar berekenen
Om het aantal resterende dagen van het huidige jaar in cel A2 te berekenen, gebruik je deze formule:
=DATUM(JAAR(A2),12,31)-A2
Druk op Enter en sleep de vulgreep om deze formule toe te passen op andere cellen indien nodig.
1.21 Bereken het verschil tussen twee tijdstippen
Om het verschil tussen twee tijdstippen te berekenen, zijn er twee eenvoudige formules die je kunt gebruiken.
Stel dat in cel A2 en B2 respectievelijk begintijd en eindtijd staan, gebruik dan deze formules:
=B2-A2
=TEKST(B2-A2,"uu:mm:ss")
Druk op Enter om het resultaat te krijgen.
Opmerking:
- Als je eindtijd-begintijd gebruikt, kun je het resultaat naar wens opmaken in het dialoogvenster Celopmaak.
- Als je TEKST(eindtijd-begintijd,"tijdnotatie") gebruikt, geef dan het gewenste tijdformaat op in de formule, bijvoorbeeld TEKST(eindtijd-begintijd,"u") geeft16 terug.
- Als de eindtijd kleiner is dan de begintijd, geven beide formules foutwaarden terug. Om dit op te lossen, kun je ABS toevoegen voor deze formules, zoals ABS(B2-A2), ABS(TEKST(B2-A2,"uu:mm:ss")), en het resultaat vervolgens als tijd opmaken.
1.22 Bereken het verschil tussen twee tijdstippen in uren/minuten/seconden
Als je het verschil tussen twee tijdstippen wilt berekenen in uren, minuten of seconden, zoals in de onderstaande schermafbeelding, volg dan dit onderdeel.
Verschil in uren tussen twee tijdstippen berekenen
Om het verschil in uren tussen twee tijdstippen in A5 en B5 te berekenen, gebruik je deze formule:
=HELE((B5-A5)*24)
Druk op Enter en formatteer het resultaat indien nodig als algemeen of getal.
Als je het verschil in decimale uren wilt berekenen, gebruik dan (eindtijd-begintijd)*24.
Verschil in minuten tussen twee tijdstippen berekenen
Om het verschil in minuten tussen twee tijdstippen in A8 en B8 te berekenen, gebruik je deze formule:
=HELE((B8-A8)*1440)
Druk op Enter en formatteer het resultaat indien nodig als algemeen of getal.
Als je het verschil in decimale minuten wilt berekenen, gebruik dan (eindtijd-begintijd)*1440.
Verschil in seconden tussen twee tijdstippen berekenen
Om het verschil in seconden tussen twee tijdstippen in A5 en B5 te berekenen, gebruik je deze formule:
=(B11-A11)*86400)
Druk op Enter en formatteer het resultaat indien nodig als algemeen of getal.
1.23 Bereken alleen het verschil in uren tussen twee tijdstippen (niet meer dan24 uur)
Als het verschil tussen twee tijdstippen niet meer dan24 uur bedraagt, kun je met de functie UUR snel het verschil in uren berekenen.
Klik op UUR voor meer informatie over deze functie.
Om het verschil in uren tussen tijdstippen in cel A14 en B14 te berekenen, gebruik je de functie UUR als volgt:
=UUR(B14-A14)
Druk op Enter om het resultaat te krijgen.
De begintijd moet kleiner zijn dan de eindtijd, anders geeft de formule de foutwaarde #GETAL! terug.
1.24 Bereken alleen het verschil in minuten tussen twee tijdstippen (niet meer dan60 minuten)
Met de functie MINUUT kun je snel alleen het verschil in minuten tussen twee tijdstippen berekenen, waarbij uren en seconden worden genegeerd.
Klik op MINUUT voor meer informatie over deze functie.
Om alleen het verschil in minuten tussen tijdstippen in cel A17 en B17 te berekenen, gebruik je de functie MINUUT als volgt:
=MINUUT(B17-A17)
Druk op Enter om het resultaat te krijgen.
De begintijd moet kleiner zijn dan de eindtijd, anders geeft de formule de foutwaarde #GETAL! terug.
1.25 Bereken alleen het verschil in seconden tussen twee tijdstippen (niet meer dan60 seconden)
Met de functie SECONDE kun je snel alleen het verschil in seconden tussen twee tijdstippen berekenen, waarbij uren en minuten worden genegeerd.
Klik op SECONDE voor meer informatie over deze functie.
Om alleen het verschil in seconden tussen tijdstippen in cel A20 en B20 te berekenen, gebruik je de functie SECONDE als volgt:
=SECONDE(B20-A20)
Druk op Enter om het resultaat te krijgen.
De begintijd moet kleiner zijn dan de eindtijd, anders geeft de formule de foutwaarde #GETAL! terug.
1.26 Bereken het verschil tussen twee tijdstippen en geef uren, minuten en seconden terug
Als je het verschil tussen twee tijdstippen wilt weergeven als xx uur xx minuten xx seconden, gebruik dan de functie TEKST zoals hieronder getoond:
Klik op TEKST voor uitleg over de argumenten en het gebruik van deze functie.
Om het verschil tussen tijdstippen in cel A23 en B23 te berekenen, gebruik je deze formule:
=TEKST(B23-A23,"u"" uur ""m"" minuten ""s"" seconden"").
Druk op Enter om het resultaat te krijgen.
Opmerking:
Deze formule berekent ook alleen het verschil in uren tot maximaal24 uur, en de eindtijd moet groter zijn dan de begintijd, anders geeft het de foutwaarde #WAARDE! terug.
1.27 Bereken het verschil tussen twee datum-tijdwaarden
Als je twee tijden hebt in het formaat mm/dd/jjjj uu:mm:ss, kun je het verschil ertussen berekenen met een van de onderstaande formules, afhankelijk van je behoefte.
Tijdsverschil tussen twee datum-tijdwaarden berekenen en resultaat weergeven in uu:mm:ss-formaat
Neem twee datum-tijdwaarden in cel A2 en B2 als voorbeeld, gebruik deze formule:
=B2-A2
Druk op Enter om een resultaat in datum-tijdformaat te krijgen, en formatteer dit resultaat vervolgens als [u]:mm:ss in de aangepaste categorie onder het tabblad Getal in Celopmaak dialoogvenster.
Verschil tussen twee datum-tijdwaarden berekenen en dagen, uren, minuten, seconden weergeven
Neem twee datum-tijdwaarden in cel A5 en B5 als voorbeeld, gebruik deze formule:
=HELE(B5-A5) & " Dagen, " & UUR(B5-A5) & " Uur, " & MINUUT(B5-A5) & " Minuten, " & SECONDE(B5-A5) & " Seconden "
Druk op Enter om het resultaat te krijgen.
Opmerking: in beide formules moet de einddatum-tijd groter zijn dan de begindatum-tijd, anders geven de formules foutwaarden terug.
1.28 Bereken tijdsverschil met milliseconden
Allereerst moet je weten hoe je de cel opmaakt om milliseconden weer te geven:
Selecteer de cellen waarin je milliseconden wilt weergeven en klik met de rechtermuisknop om te kiezen voor Celopmaak om het Celopmaak dialoogvenster te openen, kies Aangepast in de Categorie lijst onder het tabblad Getal, en typ dit uu:mm:ss.000 in het tekstvak.
Gebruik formule:
Om het verschil tussen twee tijden in cel A8 en B8 te berekenen, gebruik je deze formule:
=ABS(B8-A8)
Druk op Enter om het resultaat te krijgen.
1.29 Bereken het aantal werkuren tussen twee datums, exclusief weekenden
Soms wil je het aantal werkuren tussen twee datums tellen, waarbij weekenden (zaterdag en zondag) worden uitgesloten.
Hier zijn de werkuren vastgesteld op8 uur per dag. Om het aantal werkuren tussen twee datums in cel A16 en B16 te berekenen, gebruik je deze formule:
=NETWORKDAYS(A16,B16) *8
Druk op Enter en formatteer het resultaat indien nodig als algemeen of getal.
Voor meer voorbeelden over het berekenen van werkuren tussen twee datums, bezoek Werkuren tussen twee datums berekenen in Excel
Als je Kutools voor Excel hebt geïnstalleerd, kun je90% van de berekeningen van datum-tijdverschillen snel oplossen zonder formules te hoeven onthouden.
1.31 Bereken het verschil tussen twee datum-tijdwaarden met Data & Time Helper
Om het verschil tussen twee datum-tijdwaarden in Excel te berekenen, is alleen de Date & Time Helper voldoende.
1. Selecteer een cel waar je het berekende resultaat wilt plaatsen en klik op Kutools > Formulehulp > Date & Time Helper.
2. In het pop-upvenster Date & Time Helper, volg je onderstaande instellingen:
- Vink de optie Verschil aan;
- Selecteer de begindatum-tijd en einddatum-tijd in het gedeelte Argumentinvoer. Je kunt ook direct handmatig een datum-tijd invoeren of op het kalenderpictogram klikken om de datum te selecteren;
- Selecteer het uitvoertype van het resultaat in de keuzelijst;
- Bekijk het resultaat in het gedeelte Resultaat.
3. Klik op Ok. Het berekende resultaat wordt weergegeven, en sleep de vulgreep over de cellen waarvoor je ook wilt berekenen.
Tip:
Als je het verschil tussen twee datum-tijdwaarden wilt berekenen en het resultaat als dagen, uren en minuten wilt weergeven met Kutools voor Excel, doe dan het volgende:
Selecteer een cel waar je het resultaat wilt plaatsen en klik op Kutools > Formulehulp > Datum & Tijd > Tel dagen, uren en minuten tussen twee datums.
Ga vervolgens in het Formulehulp dialoogvenster, geef de begindatum en einddatum op en klik vervolgens op Ok.
Het verschil wordt weergegeven als dagen, uren en minuten.
Klik op Date & Time Helper voor meer informatie over deze functie.
Klik op Kutools voor Excel voor een overzicht van alle functies van deze add-in.
Klik op Gratis Download om 30-dagen gratis proefversie van Kutools voor Excel te krijgen
Als je snel het aantal weekenden, werkdagen of een specifieke weekdag tussen twee datum-tijdwaarden wilt tellen, kan de groep Formulehulp je hierbij helpen.
1. Selecteer de cel waarin je het berekende resultaat wilt plaatsen, klik op Kutools > Aantal > Aantal niet-werkdagen tussen twee datums/Aantal werkdagen tussen twee datums/Aantal dagen van de week tussen twee datums.
2. In het pop-upvenster Formulehulp geef je de begindatum en einddatum op. Als je Aantal dagen van de week tussen twee datums toepast, moet je ook de weekdag opgeven.
Om een specifieke weekdag te tellen, kun je in de opmerking zien dat je1-7 gebruikt voor Zondag-Zaterdag.
3. Klik op Ok en sleep vervolgens de vulgreep over de cellen waarvoor je het aantal weekend/werkdag/specifieke weekdag wilt tellen indien nodig.
Klik op Kutools voor Excel voor een overzicht van alle functies van deze add-in.
Klik op Gratis Download om 30-dagen gratis proefversie van Kutools voor Excel te krijgen
2. Datum en tijd optellen of aftrekken
Naast het berekenen van het verschil tussen twee datum-tijdwaarden, is het optellen of aftrekken van datum en tijd ook een veelvoorkomende bewerking in Excel. Bijvoorbeeld, je wilt wellicht de vervaldatum berekenen op basis van de productiedatum en het aantal bewaar-dagen van een product.
2.11 Voeg dagen toe aan of trek dagen af van een datum
Om een specifiek aantal dagen toe te voegen aan of af te trekken van een datum, zijn er twee verschillende methoden.
Stel dat je21 dagen wilt toevoegen aan een datum in cel A2, kies dan een van de onderstaande methoden:
Methode1: datum+dagen
Selecteer een cel en typ de formule:
=A+21
Druk op Enter om het resultaat te krijgen.
Als je21 dagen wilt aftrekken, verander dan het plusteken (+) in een minteken (-).
Methode2: Plakken speciaal
1. Typ het aantal dagen dat je wilt toevoegen in een cel, bijvoorbeeld in cel C2, en druk vervolgens op Ctrl + C om te kopiëren.
2. Selecteer vervolgens de datums waaraan je21 dagen wilt toevoegen, klik met de rechtermuisknop om het contextmenu te openen en kies Plakken speciaal....
3. In het Plakken speciaal dialoogvenster, vink Toevoegen aan (als je dagen wilt aftrekken, vink Aftrekken aan). Klik op OK.
4. Nu worden de oorspronkelijke datums weergegeven als5-cijferige getallen, formatteer ze als datums.
2.12 Voeg maanden toe aan of trek maanden af van een datum
Om maanden toe te voegen aan of af te trekken van een datum, kun je de functie EDATE gebruiken.
Klik op EDATE voor uitleg over de argumenten en het gebruik.
Stel dat je6 maanden wilt toevoegen aan de datum in cel A2, gebruik dan deze formule:
=EDATE(A2,6)
Druk op Enter om het resultaat te krijgen.
Als je6 maanden wilt aftrekken van de datum, verander6 in -6.
2.13 Voeg jaren toe aan of trek jaren af van een datum
Om n jaren toe te voegen aan of af te trekken van een datum, kun je een formule gebruiken die de functies DATUM, JAAR, MAAND en DAG combineert.
Stel dat je3 jaren wilt toevoegen aan de datum in cel A2, gebruik dan deze formule:
=DATUM(JAAR(A2) +3, MAAND(A2),DAG(A2))
Druk op Enter om het resultaat te krijgen.
Als je3 jaren wilt aftrekken van de datum, verander3 in -3.
2.14 Voeg weken toe aan of trek weken af van een datum
Om weken toe te voegen aan of af te trekken van een datum, gebruik je de algemene formule
Stel dat je4 weken wilt toevoegen aan de datum in cel A2, gebruik dan deze formule:
=A2+4*7
Druk op Enter om het resultaat te krijgen.
Als je4 weken wilt aftrekken van de datum, verander het plusteken (+) in een minteken (-).
2.15 Voeg werkdagen toe aan of trek werkdagen af van een datum, inclusief of exclusief feestdagen
In dit onderdeel wordt uitgelegd hoe je de functie WORKDAY gebruikt om werkdagen toe te voegen aan of af te trekken van een opgegeven datum, waarbij feestdagen kunnen worden uitgesloten of inbegrepen.
Bezoek WORKDAY voor meer informatie over de argumenten en het gebruik.
Werkdagen toevoegen inclusief feestdagen
In cel A2 staat de datum die je gebruikt, in cel B2 het aantal dagen dat je wilt toevoegen. Gebruik deze formule:
=WORKDAY(A2,B2)
Druk op Enter om het resultaat te krijgen.
Werkdagen toevoegen exclusief feestdagen
In cel A5 staat de datum die je gebruikt, in cel B5 het aantal dagen dat je wilt toevoegen, en in bereik D5:D8 staan de feestdagen. Gebruik deze formule:
=WORKDAY(A5,B5,D5:D8)
Druk op Enter om het resultaat te krijgen.
Opmerking:
De functie WORKDAY beschouwt zaterdag en zondag als weekend. Als jouw weekenden op andere dagen vallen, kun je de functie WORKDAY.INTL gebruiken, waarmee je zelf de weekenden kunt instellen.
Bezoek WORKDAY.INTL voor meer informatie.
Als je werkdagen wilt aftrekken van een datum, maak het aantal dagen in de formule negatief.
2.16 Voeg een specifiek aantal jaren, maanden en dagen toe aan een datum
Als je een specifiek aantal jaren, maanden en dagen wilt toevoegen aan een datum, kun je een formule gebruiken die de functies DATUM, JAAR, MAAND en DAG combineert.
Om1 jaar,2 maanden en30 dagen toe te voegen aan een datum in A11, gebruik je deze formule:
=DATUM(JAAR(A11)+1,MAAND(A11)+2,DAG(A11)+30)
Druk op Enter om het resultaat te krijgen.
Als je wilt aftrekken, verander dan alle plustekens (+) in mintekens (-).
2.21 Voeg uren/minuten/seconden toe aan of trek ze af van een datum-tijdwaarde
Hier vind je enkele formules voor het toevoegen of aftrekken van uren, minuten of seconden aan een datum-tijdwaarde.
Uren toevoegen of aftrekken van een datum-tijdwaarde
Stel dat je3 uur wilt toevoegen aan een datum-tijdwaarde (of tijd) in cel A2, gebruik dan deze formule:
=A2+3/24
Druk op Enter om het resultaat te krijgen.
Minuten toevoegen of aftrekken van een datum-tijdwaarde
Stel dat je15 minuten wilt toevoegen aan een datum-tijdwaarde (of tijd) in cel A5, gebruik dan deze formule:
=A2+15/1440
Druk op Enter om het resultaat te krijgen.
Seconden toevoegen of aftrekken van een datum-tijdwaarde
Stel dat je20 seconden wilt toevoegen aan een datum-tijdwaarde (of tijd) in cel A8, gebruik dan deze formule:
=A2+20/86400
Druk op Enter om het resultaat te krijgen.
2.22 Tel tijden op boven de24 uur
Stel dat er een Excel-tabel is waarin de werktijden van alle medewerkers in een week worden bijgehouden. Om de totale werktijd op te tellen voor de salarisberekening, kun je SOM(bereik) gebruiken om het resultaat te krijgen. Maar meestal wordt het opgetelde resultaat weergegeven als een tijd die niet boven de24 uur uitkomt, zoals in de onderstaande schermafbeelding. Hoe krijg je het juiste resultaat?
Eigenlijk hoef je alleen het resultaat op te maken als [uu]:mm:ss.
Klik met de rechtermuisknop op de resultaatcel en kies Celopmaak in het contextmenu en in het pop-upvenster Celopmaak dialoogvenster, kies Aangepast uit de lijst en typ [uu]:mm:ss in het tekstvak aan de rechterkant, klik op OK.
Het opgetelde resultaat wordt nu correct weergegeven.
2.23 Voeg werkuren toe aan een datum, exclusief weekend en feestdagen
Hier vind je een lange formule om de einddatum te berekenen op basis van het toevoegen van een specifiek aantal werkuren aan een begindatum, waarbij weekenden (zaterdag en zondag) en feestdagen worden uitgesloten.
In een Excel-tabel bevat A11 de begindatum-tijd, B11 het aantal werkuren, E11 en E13 de begin- en eindtijd van de werkdag, en E15 de feestdag die wordt uitgesloten.
Gebruik deze formule:
=WORKDAY(A11,INT(B11/8)+IF(TIME(UUR(A11),MINUUT(A11),SECONDE(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(UUR(A11),MINUUT(A11),SECONDE(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(UUR(A11),MINUUT(A11),SECONDE(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(UUR(A11),MINUUT(A11),SECONDE(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))
Druk op Enter om het resultaat te krijgen.
Als je Kutools voor Excel hebt geïnstalleerd, kun je met slechts één hulpmiddel – Date & Time Helperde meeste berekeningen voor het optellen en aftrekken van datum-tijdwaarden uitvoeren.
1. Klik op een cel waarin je het resultaat wilt weergeven en gebruik dit hulpmiddel door te klikken op Kutools > Formulehulp > Date & Time Helper.
2. In het dialoogvenster Date & Time Helper, vink je de optie Toevoegen of Aftrekken aan, kies vervolgens de cel of typ direct de datum-tijd die je wilt gebruiken in het gedeelte Argumentinvoer, geef daarna het aantal jaren, maanden, weken, dagen, uren, minuten en seconden op dat je wilt toevoegen of aftrekken, en klik op Ok. Zie screenshot:
Je kunt het berekende resultaat bekijken in het Resultaat gedeelte.
Nu wordt het resultaat weergegeven. Sleep de vulgreep over andere cellen om de resultaten te krijgen.
Klik op Date & Time Helper voor meer informatie over deze functie.
Klik op Kutools voor Excel voor een overzicht van alle functies van deze add-in.
Klik op Gratis Download om 30-dagen gratis proefversie van Kutools voor Excel te krijgen
2.41 Controleer of markeer of een datum is verlopen
Als er een lijst is met vervaldatums van producten, wil je wellicht de datums controleren en markeren die zijn verlopen op basis van vandaag, zoals in de onderstaande schermafbeelding.
Eigenlijk kan Voorwaardelijke opmaak deze taak snel uitvoeren.
1. Selecteer de datums die je wilt controleren en klik op Start > Voorwaardelijke opmaak gebruiken > Nieuwe regel.
2. In het Nieuw opmaakregel dialoogvenster, kies Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt in het Selecteer een regeltype gedeelte en typ =B2
2.42 Geef het einde van de huidige maand/eerste dag van de volgende maand terug
De vervaldatums van sommige producten vallen aan het einde van de productiemaand of op de eerste dag van de volgende maand. Om snel de vervaldatums op basis van de productiedatum te bepalen, volg je dit onderdeel.
Einde van de huidige maand berekenen
Stel dat de productiedatum in cel B13 staat, gebruik dan deze formule:
=EOMONTH(B13,0)
Druk op Enter om het resultaat te krijgen.
Eerste dag van de volgende maand berekenen
Stel dat de productiedatum in cel B18 staat, gebruik dan deze formule:
=EOMONTH(B18,0)+1
Druk op Enter om het resultaat te krijgen.
3. Leeftijd berekenen
In dit onderdeel vind je methoden om te berekenen hoe je de leeftijd bepaalt op basis van een opgegeven datum of een serienummer.
3.11 Bereken leeftijd op basis van opgegeven geboortedatum
Leeftijd als decimaal getal berekenen op basis van geboortedatum
Klik op YEARFRAC voor uitleg over de argumenten en het gebruik.
Om bijvoorbeeld de leeftijden te berekenen op basis van de lijst met geboortedatums in kolom B2:B9, gebruik je deze formule:
=YEARFRAC(B2,TODAY())
Druk op Enter en sleep de vulgreep naar beneden totdat alle leeftijden zijn berekend.
Tip:
1) Je kunt het aantal decimalen naar wens instellen in het Celopmaak dialoogvenster.
2) Als je de leeftijd op een specifieke datum wilt berekenen op basis van een opgegeven geboortedatum, vervang TODAY() door de specifieke datum tussen dubbele aanhalingstekens, bijvoorbeeld =YEARFRAC(B2,"1/1/2021")
3) Als je de leeftijd voor volgend jaar wilt berekenen op basis van de geboortedatum, tel dan1 op bij de formule, bijvoorbeeld =YEARFRAC(B2,TODAY())+1.
Leeftijd als geheel getal berekenen op basis van geboortedatum
Klik op DATEDIF voor uitleg over de argumenten en het gebruik.
Gebruik het bovenstaande voorbeeld om de leeftijd te berekenen op basis van de geboortedatums in de lijst in B2:B9. Gebruik deze formule:
=DATEDIF(B2,TODAY(),"y")
Druk op Enter en sleep de vulgreep naar beneden totdat alle leeftijden zijn berekend.
Tip:
1) Als je de leeftijd op een specifieke datum wilt berekenen op basis van een opgegeven geboortedatum, vervang TODAY() door de specifieke datum tussen dubbele aanhalingstekens, bijvoorbeeld =DATEDIF(B2,"1/1/2021","y").
2) Als je de leeftijd voor volgend jaar wilt berekenen op basis van de geboortedatum, tel dan1 op bij de formule, bijvoorbeeld =DATEDIF(B2,TODAY(),"y")+1.
3.12 Bereken leeftijd in jaren, maanden en dagen op basis van opgegeven geboortedatum
Als je de leeftijd wilt berekenen op basis van een opgegeven geboortedatum en het resultaat wilt weergeven als xx jaren, xx maanden, xx dagen, zoals in de onderstaande schermafbeelding, kun je deze lange formule gebruiken.
Om de leeftijd in jaren, maanden en dagen te berekenen op basis van de geboortedatum in cel B12, gebruik je deze formule:
=DATEDIF(B12,TODAY(),"Y")&" Jaren, "&DATEDIF(B12,TODAY(),"YM")&" Maanden, "&DATEDIF(B12,TODAY(),"MD")&" Dagen"
Druk op Enter om de leeftijd te krijgen en sleep de vulgreep naar beneden naar andere cellen.
Tip:
Als je de leeftijd op een specifieke datum wilt berekenen op basis van een opgegeven geboortedatum, vervang TODAY() door de specifieke datum tussen dubbele aanhalingstekens, bijvoorbeeld =DATEDIF(B12,"1/1/2021","Y")&" Jaren, "&DATEDIF(B12,"1/1/2021","YM")&" Maanden, "&DATEDIF(B12,"1/1/2021","MD")&" Dagen".
3.13 Bereken leeftijd bij geboorte vóór1/1/1900
In Excel kun je geen datum vóór1/1/1900 invoeren of correct berekenen. Maar als je de leeftijd van een bekend persoon wilt berekenen op basis van een opgegeven geboortedatum (voor1/1/1900) en overlijdensdatum, kan alleen een VBA-code je hierbij helpen.
1. Druk op Alt + F11 om het venster Microsoft Visual Basic for Applications te openen, klik op het tabblad Invoegen en kies Module om een nieuw module te maken.
2. Kopieer en plak vervolgens onderstaande code in de nieuwe module.
VBA: Leeftijd berekenen vóór1/1/1900
Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
Dim xSMonth As Integer
Dim xSDay As Integer
Dim xSYear As Integer
Dim xEMonth As Integer
Dim xEDay As Integer
Dim xEYear As Integer
Dim xAge As Integer
If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
xAge = xEYear - xSYear
If xSMonth > xEMonth Then
xAge = xAge - 1
ElseIf xSMonth = xEMonth Then
If xSDay > xEDay Then xAge = xAge - 1
End If
If xAge < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = xAge
End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
Dim I As Long
Dim K As Long
Y = 0
M = 0
D = 0
GetDate = True
On Error Resume Next
I = InStr(1, DateStr, "/")
M = CLng(Left(DateStr, I - 1))
D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
GetDate = False
End If
End Function
3. Sla de code op, ga terug naar het werkblad en selecteer een cel om de berekende leeftijd te plaatsen, typ =AgeFunc(geboortedatum,overlijdensdatum), in dit geval: =AgeFunc(B22,C22), druk op Enter om de leeftijd te krijgen. Gebruik de vulgreep om deze formule toe te passen op andere cellen indien nodig.
Als je Kutools voor Excel in Excel hebt geïnstalleerd, kun je de Date & Time Helper tool gebruiken om de leeftijd te berekenen.
1. Selecteer een cel waarin je de berekende leeftijd wilt plaatsen en klik op Kutools > Formulehulp > Date & Time Helper.
2. In het dialoogvenster Date & Time Helper:
- 1) Vink de optie Leeftijd aan;
- 2) Kies de cel met de geboortedatum of voer deze direct in, of klik op het kalenderpictogram om de geboortedatum te selecteren;
- 3) Kies de optie Vandaag als je de huidige leeftijd wilt berekenen, kies de optie Specifieke datum en voer de datum in als je de leeftijd in het verleden of de toekomst wilt berekenen;
- 4) Geef het uitvoertype op in de keuzelijst;
- 5) Bekijk het resultaat. Klik op Ok.
Klik op Date & Time Helper voor meer informatie over deze functie.
Klik op Kutools voor Excel voor een overzicht van alle functies van deze add-in.
Klik op Gratis Download om 30-dagen gratis proefversie van Kutools voor Excel te krijgen
3.31 Haal geboortedatum uit ID-nummer
Als er een lijst is met ID-nummers waarvan de eerste6 cijfers de geboortedatum aangeven, bijvoorbeeld920315330 betekent geboortedatum15-03-1992, hoe kun je snel de geboortedatum in een andere kolom krijgen?
Neem nu de lijst met ID-nummers die begint in cel C2 als voorbeeld en gebruik deze formule:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Druk op Enter toets. Sleep vervolgens de vulgreep naar beneden om andere resultaten te krijgen.
Opmerking:
In de formule kun je de verwijzing aanpassen naar jouw situatie. Bijvoorbeeld, als het ID-nummer wordt weergegeven als13219920420392, is de geboortedatum20-04-1992. Je kunt de formule aanpassen naar =MID(C2,8,2)&"/"&MID(C2,10,2)&"/"&MID(C2,4,4) om het juiste resultaat te krijgen.
3.32 Bereken leeftijd op basis van ID-nummer
Als er een lijst is met ID-nummers waarvan de eerste6 cijfers de geboortedatum aangeven, bijvoorbeeld920315330 betekent geboortedatum15-03-1992, hoe kun je snel de leeftijd berekenen op basis van elk ID-nummer in Excel?
Neem nu de lijst met ID-nummers die begint in cel C2 als voorbeeld en gebruik deze formule:
=DATEDIF(DATUM(ALS(LINKS(C2,2)>TEKST(VANDAAG(),"JJ"),"19"&LINKS(C2,2),"20"&LINKS(C2,2)),MID(C2,3,2),MID(C2,5,2)),VANDAAG(),"j")
Druk op Enter toets. Sleep vervolgens de vulgreep naar beneden om andere resultaten te krijgen.
Opmerking:
In deze formule wordt, als het jaar kleiner is dan het huidige jaar, het jaar beschouwd als beginnend met20, bijvoorbeeld200203943 wordt beschouwd als het jaar2020; als het jaar groter is dan het huidige jaar, wordt het jaar beschouwd als beginnend met19, bijvoorbeeld920420392 wordt beschouwd als het jaar1992.
Meer Excel-handleidingen:
Meerdere werkboeken/werkbladen samenvoegen tot één
Deze handleiding behandelt vrijwel alle scenario's voor het samenvoegen die je kunt tegenkomen en biedt professionele oplossingen.
Tekst-, getal- en datumcellen splitsen (in meerdere kolommen scheiden)
Deze handleiding is verdeeld in drie delen: tekstcellen splitsen, getalcellen splitsen en datumcellen splitsen. Elk deel bevat verschillende voorbeelden om je te helpen bij het uitvoeren van splitsingen wanneer je met hetzelfde probleem te maken krijgt.
Inhoud van meerdere cellen samenvoegen zonder gegevensverlies in Excel
Deze handleiding richt zich op het extraheren op een specifieke positie in een cel en verzamelt verschillende methoden om tekst of getallen uit een cel te halen op basis van een specifieke positie in Excel.
Twee kolommen vergelijken op overeenkomsten en verschillen in Excel
Dit artikel behandelt de meeste mogelijke scenario's voor het vergelijken van twee kolommen die je kunt tegenkomen, en hopelijk helpt het je verder.
De Beste Office Productiviteitstools
Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%
- Super Formulebalk (bewerk eenvoudig meerdere regels tekst en formules); Leeslayout (lees en bewerk eenvoudig grote aantallen cellen); Plakken in gefilterd bereik...
- Cellen/Rijen/Kolommen samenvoegen en gegevens behouden; Celinhoud splitsen; Dubbele rijen combineren en optellen/gemiddelde berekenen... Voorkom dubbele cellen; Bereiken vergelijken...
- Selecteer dubbele of unieke rijen; Selecteer lege rijen (alle cellen zijn leeg); Super Zoeken en Fuzzy Zoeken in veel werkboeken; Willekeurige selectie...
- Exacte kopie van meerdere cellen zonder formuleverwijzing te wijzigen; Automatisch verwijzingen naar meerdere werkbladen maken; Opsommingstekens, selectievakjes en meer invoegen...
- Favoriete en snel formules, bereiken, grafieken en afbeeldingen invoegen; Cellen versleutelen met wachtwoord; Mailinglijst maken en e-mails versturen...
- Tekst extraheren, Tekst toevoegen, Verwijder tekens op een specifieke positie, Spaties verwijderen; Maak en print statistieken per pagina; Converteer tussen celinhoud en opmerkingen...
- Superfilter (filter schema's opslaan en toepassen op andere werkbladen); Geavanceerd sorteren op maand/week/dag, frequentie en meer; Speciaal filteren op vet, cursief...
- Werkboeken en werkbladen combineren; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere werkbladen; Batchconversie xls, xlsx en PDF...
- Draaitabelgroepering op weeknummer, dag van de week en meer... Toon ontgrendelde, vergrendelde cellen met verschillende kleuren; Markeer cellen die een formule/naam bevatten...
- Schakel tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project in.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
