Excel-handleiding: Datum/tijd-berekeningen (verschil berekenen, leeftijd, optellen/aftrekken)
In Excel wordt het Datum en tijd-berekeningsproces vaak gebruikt, zoals het berekenen van het verschil tussen twee datums/tijdstippen, datum/tijd optellen of aftrekken, leeftijd bepalen op basis van een geboortedatum, enzovoort. In deze handleiding worden bijna alle scenario’s rondom datum/tijd-berekeningen beschreven, inclusief de bijbehorende methoden.
In deze handleiding gebruik ik voorbeelden om de methoden duidelijk uit te leggen. U kunt de verwijzingen eenvoudig aanpassen aan uw eigen situatie wanneer u de onderstaande VBA-code of formules toepast.
1. Bereken het verschil tussen twee datums of tijdstippen.
Het berekenen van het verschil tussen twee datums of tijdstippen is waarschijnlijk het meest voorkomende uitdaging bij datum- en tijdgerelateerde berekeningen in het dagelijks Excel-gebruik. De onderstaande voorbeelden helpen u efficiënter om te gaan met dit soort situaties.
1,11 Bereken het verschil tussen twee datums in dagen/maanden/jaren/weken
Met de Excel-functie DATEDIF berekent u in een handomdraai het verschil tussen twee datums – in dagen, maanden, jaren of weken.
Klik voor meer informatie over de DATEDIF-functie
Verschil in dagen tussen twee datums
Gebruik de volgende formule om het verschil in dagen tussen de datums in cel A2 en B2 te berekenen:
=DATEDIF(A2,B2,„d")
Druk op de Enter-toets om het resultaat te verkrijgen.
Verschil in maanden tussen twee datums
Gebruik de volgende formule om het verschil in maanden tussen de datums in cel A5 en B5 te berekenen:
=DATEDIF(A5,B5,„m")
Druk op de Enter-toets om het resultaat te verkrijgen.
Verschil in jaren tussen twee datums
Gebruik de volgende formule om het verschil in jaren tussen de datums in cel A8 en B8 te berekenen:
=DATEDIF(A8,B8,„y")
Druk op de Enter-toets om het resultaat te verkrijgen.
Verschil in weken tussen twee datums
Gebruik de volgende formule om het verschil in weken tussen de datums in cel A11 en B11 te berekenen:
=DATEDIF(A11,B11,„d")/7
Druk op de Enter-toets om het resultaat te verkrijgen.
Opmerking:
1) Wanneer u bovenstaande formule gebruikt om het verschil in weken te berekenen, wordt het resultaat mogelijk weergegeven in datumnotatie. Formatteer het resultaat dan naar Algemeen of Getal, afhankelijk van uw voorkeur.
2) Wanneer u bovenstaande formule gebruikt om het verschil in weken te berekenen, kan het resultaat een decimaal getal zijn. Als u een geheel weeknummer wilt, kunt u de functie AFRONDEN.NAAR.BENEDEN toevoegen, zoals hieronder wordt getoond, om het volledige aantal weken te verkrijgen:
=ROUNDDOWN(DATEDIF(A11,B11,„d")/7,0)
1,12 Bereken het aantal maanden tussen twee datums, waarbij jaren en dagen worden genegeerd
Als u alleen het verschil in maanden wilt berekenen, zonder rekening te houden met jaren en dagen tussen twee datums—zoals in de onderstaande afbeelding wordt getoond—dan helpt de volgende formule u daarbij.
=DATEDIF(A2,B2,„ym")
Druk op de Enter-toets om het resultaat te verkrijgen.
A2 is de startdatum en B2 is de einddatum.
1,13 Bereken het aantal dagen tussen twee datums, waarbij jaren en maanden worden genegeerd
Als u alleen het verschil in dagen wilt berekenen tussen twee datums, zonder rekening te houden met jaren en maanden, zoals in de onderstaande afbeelding wordt getoond, helpt de volgende formule u daarbij.
=DATEDIF(A5,B5,„md")
Druk op de Enter-toets om het resultaat te verkrijgen.
A5 is de startdatum en B5 is de einddatum.
1,14 Bereken het verschil tussen twee datums en geef jaren, maanden en dagen terug
Als u het verschil tussen twee datums wilt weergeven als xx jaar, xx maanden en xx dagen – zoals in de onderstaande afbeelding – dan is daar ook een formule voor beschikbaar.
=DATEDIF(A8, B8, "y") &„ years, "&DATEDIF(A8, B8, "ym") &„ months, " &DATEDIF(A8, B8, "md") &„ days"
Druk op de Enter-toets om het resultaat te verkrijgen.
A8 is de startdatum 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, vervangt u de einddatum in bovenstaande formules door VANDAAG(). Hieronder ziet u een voorbeeld van het berekenen van het verschil in dagen tussen een datum in het verleden en vandaag.
=DATEDIF(A11,TODAY(),„d")
Druk op de Enter-toets om het resultaat te verkrijgen.
Opmerking: als u het verschil tussen een toekomstige datum en vandaag wilt berekenen, stelt u de begindatum in op vandaag en gebruikt u de toekomstige datum als einddatum, zoals hieronder:
=DATEDIF(TODAY(),A14,„d")
Let op: de begindatum moet vóór de einddatum liggen in de DATEDIF-functie, anders krijgt u de foutwaarde #GETAL!.
1,16 Bereken werkdagen met of zonder vakantiedagen tussen twee datums
Soms wilt u het aantal werkdagen tussen twee opgegeven datums tellen, al dan niet inclusief vakantiedagen.
In dit gedeelte gebruikt u de functie NETTO.WERKDAGEN.INTL:
Klik op NETTO.WERKDAGEN.INTL om de argumenten en het gebruik ervan te bekijken.
Tel werkdagen mét vakantiedagen
Gebruik de volgende formule om het aantal werkdagen mét vakantiedagen tussen de datums in cel A2 en B2 te tellen:
=NETWORKDAYS.INTL(A2,B2)
Druk op de Enter-toets om het resultaat te verkrijgen.
Tel werkdagen zonder vakantiedagen
Gebruik de volgende formule om het aantal werkdagen tussen de datums in cel A2 en B2 te tellen, exclusief de vakantiedagen in bereik D5:D9:
=NETTO.WERKDAGEN.INTL(A5;B5;1;D5:D9)
Druk op de Enter-toets om het resultaat te verkrijgen.
Opmerking:
In de bovenstaande formules worden zaterdag en zondag beschouwd als weekenddagen. Hebt u andere weekenddagen? Pas het argument [weekend] dan naar wens aan.
1,17 Bereken weekenddagen tussen twee datums
Wilt u het aantal weekenddagen tussen twee datums tellen? Dan helpen de functies SOMPRODUCT of SOM u daarbij.
Om het aantal weekenddagen (zaterdag en zondag) tussen de datums in cel A12 en B12 te tellen:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Of
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Druk op de Enter-toets om het resultaat te verkrijgen.
1,18 Bereken een specifieke weekdag tussen twee datums
Om het aantal keren te tellen dat een specifieke weekdag, zoals maandag, tussen twee datums voorkomt, combineert u de functies INTEGER en WEEKDAG.
Cel A15 en B15 bevatten de twee datums tussen welke u het aantal maandagen wilt tellen. Gebruik hiervoor de volgende formule:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Druk op de Enter-toets om het resultaat te verkrijgen.
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 en 7 is zaterdag)
1,19 Bereken Resterende dagen in maand/jaar
Soms wilt u weten hoeveel Resterende dagen er in een maand of jaar zijn op basis van een opgegeven datum, zoals in de onderstaande afbeelding wordt getoond:
Haal Resterende dagen op in de huidige maand
Klik op EOMONTH om het argument en het gebruik te bekijken.
Gebruik de volgende formule om de Resterende dagen van de huidige maand in cel A2 te verkrijgen:
=EOMONTH(A2,0)-A2
Druk op Enter en sleep, indien nodig, de automatische vulgreep naar andere cellen.
Tip: De resultaten worden mogelijk weergegeven in datumnotatie; wijzig deze dan naar de notatie Algemeen of Getal.
Verkrijg de Resterende dagen in het huidige jaar
Gebruik de volgende formule om de Resterende dagen van het huidige jaar in cel A2 te verkrijgen:
=DATE(YEAR(A2),12,31)-A2
Druk op Enter en sleep, indien nodig, de automatische vulgreep naar andere cellen.
1,21 Bereken het verschil tussen twee tijdstippen
Om het verschil tussen twee tijdstippen te berekenen, staan hier twee eenvoudige formules klaar om u te helpen.
Stel dat cel A2 en B2 respectievelijk start_tijd en eind_tijd bevatten; gebruik dan de volgende formules:
=B2-A2
=TEXT(B2-A2,„hh:mm:ss")
Druk op de Enter-toets om het resultaat te verkrijgen.
Opmerking:
- Wanneer u eindtijd – begintijd gebruikt, kunt u het resultaat in het dialoogvenster Celopmaak instellen naar elk gewenst tijdformaat opmaken.
- Wanneer u TEKST(eindtijd–begintijd;„tijdformaat") gebruikt, voert u het gewenste tijdformaat direct in de formule in—bijvoorbeeld TEKST(eindtijd–begintijd;„u") geeft 16 terug.
- Als de eindtijd kleiner is dan de begintijd, leveren beide formules foutwaarden op. Los dit eenvoudig op door ABS te gebruiken in deze formules, zoals ABS(B2–A2) of ABS(TEKST(B2–A2;„uu:mm:ss")), en formatteer het resultaat daarna als tijd.
1,22 Bereken het verschil tussen twee tijdstippen in uren/minuten/seconden
Als u het verschil tussen twee tijdstippen in uren, minuten of seconden wilt berekenen, zoals in de onderstaande afbeelding wordt weergegeven, volg dan deze instructies.
Verkrijg het verschil in uren tussen twee tijdstippen
Gebruik de volgende formule om het verschil in uren tussen de tijdstippen in A5 en B5 te verkrijgen:
=INT((B5-A5)*24)
Druk op Enter en formatteer het resultaat eerst met de tijdnotatie, vervolgens als Algemeen of Getal.
Wilt u het verschil in decimale uren berekenen? Gebruik dan (eindtijd – starttijd) × 24.
Verkrijg het verschil in minuten tussen twee tijdstippen
Gebruik de volgende formule om het verschil in minuten tussen de tijdstippen in A8 en B8 te verkrijgen:
=INT((B8-A8)*1440)
Druk op Enter en formatteer het resultaat eerst met de tijdnotatie, daarna als Algemeen of Getal.
Wilt u het verschil in decimale minuten berekenen? Gebruik dan (eind_tijd – start_tijd) × 1440.
Verkrijg het verschil in seconden tussen twee tijdstippen
Gebruik de volgende formule om het verschil in seconden tussen de tijdstippen in A5 en B5 te verkrijgen:
=(B11-A11)*86400)
Druk op Enter en formatteer het resultaat eerst met de tijdnotatie, daarna als Algemeen of Getal.
1,23 Bereken alleen het verschil in uren tussen twee tijdstippen (niet meer dan 24 uur)
Als het verschil tussen twee tijdstippen niet meer dan 24 uur bedraagt, geeft de functie UUR snel het verschil in uren tussen die tijdstippen.
Klik op UUR voor meer informatie over deze functie.
Gebruik de functie UUR als volgt om het verschil in uren tussen de tijdstippen in cel A14 en B14 te verkrijgen:
=HOUR(B14-A14)
Druk op de Enter-toets om het resultaat te verkrijgen.
De starttijd moet kleiner zijn dan de eindtijd; anders geeft de formule de fout #GETAL! terug.
1,24 Bereken alleen het verschil in minuten tussen twee tijdstippen (niet meer dan 60 minuten)
Met de functie MINUUT krijgt u snel en eenvoudig alleen het verschil in minuten tussen deze twee tijdstippen, terwijl uren en seconden worden genegeerd.
Klik op MINUUT voor meer informatie over deze functie.
Gebruik de functie MINUUT als volgt om alleen het verschil in minuten tussen de tijdstippen in cel A17 en B17 te verkrijgen:
=MINUTE(B17-A17)
Druk op de Enter-toets om het resultaat te verkrijgen.
De starttijd 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 dan 60 seconden)
Met de functie SECONDE krijgt u razendsnel alleen het verschil in seconden tussen deze twee tijdstippen, terwijl uren en minuten worden genegeerd.
Klik op SECONDE voor meer informatie over deze functie.
Gebruik de functie SECONDE als volgt om alleen het verschil in seconden tussen de tijdstippen in cel A20 en B20 te verkrijgen:
=SECOND(B20-A20)
Druk op de Enter-toets om het resultaat te verkrijgen.
De starttijd 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 weer
Als u het verschil tussen twee tijdstippen wilt weergeven als xx uur xx minuten xx seconden, gebruikt u de functie TEKST zoals hieronder wordt getoond:
Klik op TEKST om de argumenten en het gebruik van deze functie te bekijken.
Gebruik de volgende formule om het verschil tussen de tijdstippen in cel A23 en B23 te berekenen:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds"„").
Druk op de Enter-toets om het resultaat te verkrijgen.
Opmerking:
Deze formule berekent uitsluitend het verschil in uren wanneer dat niet meer dan 24 uur bedraagt, en de eindtijd moet groter zijn dan de starttijd; anders geeft de formule de foutwaarde #WAARDE! terug.
1,27 Bereken het verschil tussen twee datum/tijd-waarden
Als u het verschil wilt berekenen tussen twee tijdstippen in de notatie mm/dd/jjjj uu:mm:ss, kunt u een van de onderstaande formules gebruiken, afhankelijk van uw specifieke behoefte.
Verkrijg het tijdsverschil tussen twee datum/tijd-waarden en geef het resultaat weer in de notatie uu:mm:ss
Neem als voorbeeld twee datum/tijd-waarden in cel A2 en B2; gebruik dan de volgende formule:
=B2-A2
Druk op Enter om een resultaat in datum/tijd-notatie te verkrijgen en formatteer dit vervolgens als [u]:mm:ss in de categorie Aangepast onder het Getal-tabblad in het Celopmaak instellen-dialoogvenster.
Verkrijg het verschil tussen twee datum/tijd-waarden en geef dagen, uren, minuten en seconden weer
Neem als voorbeeld twee datum/tijd-waarden in cel A5 en B5; gebruik dan de volgende formule:
=INT(B5-A5) & „ Days, " & HOUR(B5-A5) & „ Hours, " & MINUTE(B5-A5) & „ Minutes, " & SECOND(B5-A5) & „ Seconds "
Druk op de Enter-toets om het resultaat te verkrijgen.
Opmerking: in beide formules moet eind_datumtijd groter zijn dan start_datumtijd; anders geven de formules foutwaarden terug.
1,28 Bereken het tijdsverschil met milliseconden
Eerst moet u weten hoe u de cel moet formatteren om milliseconden weer te geven:
Selecteer de cellen waarin u milliseconden wilt weergeven, klik met de rechtermuisknop en kies Celopmaak instellen om het Celopmaak instellen-dialoogvenster te openen. Selecteer Aangepast in de Categorie-lijst onder het tabblad Getal en typ uu:mm:ss.000 in het tekstvak.
Gebruik de formule:
Gebruik de volgende formule om het verschil tussen de tijdstippen in cel A8 en B8 te berekenen:
=ABS(B8-A8)
Druk op de Enter-toets om het resultaat te verkrijgen.
1,29 Bereken het aantal werkuren tussen twee datums, exclusief weekenden
Soms moet u de werktijd tussen twee datums berekenen, exclusief weekenddagen (zaterdag en zondag).
De standaard werktijd is vastgesteld op 8 uur per dag. Gebruik de volgende formule om de werktijd tussen de datums in cel A16 en B16 te berekenen:
=NETWORKDAYS(A16,B16) * 8
Druk op Enter en formatteer het resultaat vervolgens als Algemeen of Getal.
Voor meer voorbeelden over het berekenen van werktijd tussen twee datums, bezoekt u Bereken werktijd tussen twee datums in Excel
Als u Kutools voor Excel in Excel hebt geïnstalleerd, lost u 90 procent van de berekeningen van datum- en tijdsverschillen razendsnel op—zonder dat u formules hoeft te onthouden.
1,31 Bereken het verschil tussen twee datum/tijd-waarden met Hulp bij datum en tijd
Om het verschil tussen twee datum/tijd-waarden in Excel te berekenen, is de Datum en Tijd Assistent alles wat je nodig hebt.
1. Selecteer een cel waarin u het berekende resultaat wilt plaatsen en klik op Kutools > Formulehulp > Datum en Tijd Assistent.
2. Volg in het verschijnende Datum en Tijd Assistent-dialoogvenster de onderstaande instellingen:
- Selecteer Verschiloptie;
- Selecteer de begin- en einddatum met tijdstip in het gedeelte Argumentinvoer, u kunt ook handmatig een datum met tijdstip invoeren in het invoervak of op het kalenderpictogram klikken om een datum te selecteren;
- Selecteer het Uitvoertype in de Keuzelijst;
- Bekijk een voorbeeld van het resultaat in het gedeelte Resultaat.
3. Klik op OK. Het berekende resultaat verschijnt direct—sleep de automatische vulgreep naar de cellen waarvoor u dezelfde berekening wilt toepassen.
Tip:
Als u het verschil tussen twee datum/tijd-waarden wilt verkrijgen en het resultaat wilt weergeven als dagen, uren en minuten met Kutools voor Excel, gaat u als volgt te werk:
Selecteer een cel waarin u het resultaat wilt plaatsen en klik op Kutools > Formulehulp > Datum en tijd > Aantal dagen, uren en minuten tussen twee datums.
Geef vervolgens in het Formulehulp-dialoogvenster de startdatum en einddatum op en klik op OK.
Het verschilresultaat wordt weergegeven in dagen, uren en minuten.
Klik op Datum en Tijd Assistent om meer te weten te komen over het gebruik van deze functie.
Klik op Kutools voor Excel om alle functies van deze invoegtoepassing te ontdekken.
Klik op Gratis downloaden om een gratis proefversie van 30 dagen van Kutools voor Excel te krijgen.
Wilt u snel het aantal weekenddagen, werkdagen of een specifieke weekdag tussen twee datums tellen? Dan helpt de groep Formulehulp van Kutools voor Excel u daarbij.
1. Selecteer de cel waarin het berekende resultaat moet worden geplaatst en klik op Kutools > Statistisch > Aantal niet-werkdagen tussen twee datums / Aantal werkdagen tussen twee datums / Aantal dagen van de week tussen twee datums.
2. Geef in het verschijnende Formulehulp-dialoogvenster de startdatum en einddatum op; als u Aantal dagen van de week tussen twee datums gebruikt, moet u ook de weekdag opgeven.
Om een specifieke weekdag te tellen, raadpleegt u de opmerking voor het gebruik van 1 t/m 7 om respectievelijk zondag tot en met zaterdag aan te geven.
3. Klik op OK en sleep, indien nodig, de automatisch-aanvullen-greep over de cellen waarin het aantal weekenddagen, werkdagen of specifieke weekdagen moet worden geteld.
Klik op Kutools voor Excelom alle functies van deze invoegtoepassing te ontdekken.
Klik op Gratis downloadenom een gratis 30-daagse proefversie van Kutools voor Excel te krijgen.
2. Datum en tijd optellen of aftrekken
Naast het berekenen van het verschil tussen twee datums is het optellen of aftrekken van dagen eveneens een veelvoorkomende datum- en tijdberekening in Excel. Zo kunt u bijvoorbeeld de vervaldatum bepalen op basis van de productiedatum en het aantal houdbaarheidsdagen van een product.
2,11 Dagen optellen bij of aftrekken van een datum
Er zijn twee verschillende methoden om een specifiek aantal dagen bij een datum op te tellen of ervan af te trekken.
Stel dat u 21 dagen wilt optellen bij de datum in cel A2; kies dan één van de onderstaande methoden om dit eenvoudig op te lossen.
Methode 1 datum+dagen
Selecteer een cel en typ de formule:
=A+21
Druk op de Enter-toets om het resultaat te verkrijgen.
Wilt u 21 dagen aftrekken? Vervang dan het plusteken (+) door een minteken (-).
Methode 2 Plakken speciaal
1. Typ het aantal dagen dat u wilt optellen in een cel, bijvoorbeeld in cel C2, en druk vervolgens op Ctrl+C om het te kopiëren.
2. Selecteer vervolgens de datums waaraan u 21 dagen wilt toevoegen, klik met de rechtermuisknop om het contextmenu te openen en kies Plakken speciaal....
3. In het Plakken speciaal-dialoogvenster vinkt u de optie Optellenaan (als u dagen wilt aftrekken, vinkt u de optie)Aftrekken aan). Klik op OK.
4. De oorspronkelijke datums worden nu weergegeven als vijfcijferige getallen; formatteer ze opnieuw als datums.
2,12 Maanden optellen bij of aftrekken van een datum
Gebruik de functie DATUM.TOEGEVOEGD om maanden bij een datum op te tellen of ervan af te trekken.
Klik op DATUM.TOEGEVOEGD om de argumenten en het gebruik ervan te bestuderen.
Stel dat u 6 maanden wilt optellen bij de datum in cel A2; gebruik dan de volgende formule:
=EDATE(A2,6)
Druk op de Enter-toets om het resultaat te verkrijgen.
Als u 6 maanden van de datum wilt aftrekken, wijzigt u 6 in -6.
2,13 Jaren optellen bij of aftrekken van een datum
Om n jaar bij een datum op te tellen of ervan af te trekken, combineert u de functies DATUM, JAAR, MAAND en DAG in één formule.
Stel dat u 3 jaar wilt optellen bij de datum in cel A2; gebruik dan de volgende formule:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Druk op de Enter-toets om het resultaat te verkrijgen.
Als u 3 jaar van de datum wilt aftrekken, wijzigt u 3 in -3.
2,14 Weken optellen bij of aftrekken van een datum
Om weken bij een datum op te tellen of ervan af te trekken, is de algemene formule
Stel dat u 4 weken wilt optellen bij de datum in cel A2; gebruik dan de volgende formule:
=A2+4*7
Druk op de Enter-toets om het resultaat te verkrijgen.
Als u 4 weken van de datum wilt aftrekken, vervangt u het plusteken (+) door een minteken (-).
2,15 Werkdagen optellen bij of aftrekken van een datum, inclusief of exclusief feestdagen
In dit gedeelte leest u hoe u met de functie WERKDAG werkdagen bij een opgegeven datum kunt optellen of ervan aftrekken – exclusief of inclusief feestdagen.
Bezoek WERKDAG om meer details te weten te komen over de argumenten en het gebruik ervan.
Werkdagen optellen, inclusief feestdagen
In cel A2 staat de datum die u wilt gebruiken; in cel B2 het aantal dagen dat u hieraan wilt toevoegen. Gebruik hiervoor de volgende formule:
=WORKDAY(A2,B2)
Druk op de Enter-toets om het resultaat te verkrijgen.
Werkdagen optellen, exclusief feestdagen
In cel A5 staat de datum die u wilt gebruiken; in cel B5 het aantal dagen dat u wilt optellen; en in het bereik D5:D8 de feestdagen. Gebruik de volgende formule:
=WORKDAY(A5,B5,D5:D8)
Druk op de Enter-toets om het resultaat te verkrijgen.
Opmerking:
De functie WERKDAG beschouwt standaard zaterdag en zondag als weekenddagen. Als uw weekend ook op zaterdag en zondag valt, kunt u de functie WERKDAG.INTL gebruiken om uw eigen weekenddagen op te geven.
Bezoek WERKDAG.INTL voor meer informatie.
Als u werkdagen van een datum wilt aftrekken, wijzigt u het aantal dagen in de formule eenvoudigweg in een negatief getal.
2,16 Specifiek jaar, maand en dagen optellen bij of aftrekken van een datum
Als u een specifiek aantal jaren, maanden en dagen bij een datum wilt optellen, helpt een formule die de functies DATUM, JAAR, MAAND en DAG combineert u daarbij.
Om 1 jaar, 2 maanden en 30 dagen op te tellen bij een datum in A11, gebruikt u de volgende formule:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Druk op de Enter-toets om het resultaat te verkrijgen.
Wilt u aftrekken? Vervang dan alle plustekens (+) door mintekens (-).
2,21 Uren/minuten/seconden optellen bij of aftrekken van een datum/tijd
Hieronder vindt u handige formules om uren, minuten of seconden bij een datum/tijd op te tellen of ervan af te trekken.
Uren optellen bij of aftrekken van een datum/tijd
Stel dat u 3 uur wilt optellen bij een datum/tijd (kan ook alleen een tijd zijn) in cel A2; gebruik dan de volgende formule:
=A2+3/24
Druk op de Enter-toets om het resultaat te verkrijgen.
Minuten optellen bij of aftrekken van een datum/tijd
Stel dat u 15 minuten wilt optellen bij een datum/tijd (kan ook alleen een tijd zijn) in cel A5; gebruik dan de volgende formule:
=A2+15/1440
Druk op de Enter-toets om het resultaat te verkrijgen.
Seconden optellen bij of aftrekken van een datum/tijd
Stel dat u 20 seconden wilt optellen bij een datum/tijd (kan ook alleen een tijd zijn) in cel A8; gebruik dan de volgende formule:
=A2+20/86400
Druk op de Enter-toets om het resultaat te verkrijgen.
2,22 Sommeer tijden die langer zijn dan 24 uur
Stel dat u een Excel-tabel heeft met de werktijden van alle medewerkers in een week. Om de totale werktijd op te tellen voor het berekenen van de salarissen, kunt u SOM(bereik) gebruiken om het resultaat te verkrijgen. Meestal wordt het opgetelde resultaat echter weergegeven als een tijdsduur die niet langer is dan 24 uur, zoals in de onderstaande schermafbeelding te zien is. Hoe krijgt u dan het juiste resultaat?
U hoeft het resultaat eigenlijk alleen te formatteren als [uu]:mm:ss.
Klik met de rechtermuisknop op de cel met het resultaat en kies Celopmaak instellen in het contextmenu. In het verschijnende Celopmaak instellen-dialoogvenster selecteert u Aangepast in de lijst en typt u [uu]:mm:ss in het tekstvak aan de rechterkant. Klik vervolgens op OK.
Het opgetelde resultaat wordt nu correct weergegeven.
2,23 Voeg werkuren toe aan een datum, exclusief weekenden en feestdagen
Hieronder vindt u een handige formule waarmee u de einddatum nauwkeurig kunt berekenen door een specifiek aantal werkuren toe te voegen aan de startdatum — inclusief automatische uitsluiting van weekenddagen (zaterdag en zondag) en feestdagen.
In een Excel-tabel bevat cel A11 de startdatum en -tijd, en cel B11 het aantal werkuren. De begin- en eindtijden van de werkdag staan respectievelijk in cel E11 en E13, en cel E15 bevat de feestdag die moet worden uitgesloten.
Gebruik de volgende formule:
=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))
Druk op de Enter-toets om het resultaat te verkrijgen.
Als u Kutools voor Excel hebt geïnstalleerd, doet één handige tool –Datum- en tijdassistent– het zware werk bij vrijwel alle berekeningen met optellen en aftrekken van datum en tijd.
1. Klik op een cel waarin u het resultaat wilt weergeven en pas dit hulpmiddel toe door te klikken op Kutools > Formulehulp > Datum en Tijd Assistent.
2. In het Datum en Tijd Assistent-dialoogvenster selecteert u naar behoefte de optie Optellen of Aftrekken. Kies vervolgens de cel of typ direct de datum/tijd die u wilt gebruiken in het gedeelte Argumentinvoer, geef daarna het aantal jaren, maanden, weken, dagen, uren, minuten en seconden op dat u wilt optellen of aftrekken, en klik op OK. Zie de schermafbeelding:
U kunt het berekende resultaat alvast bekijken in het gedeelte Resultaat.
Het resultaat wordt nu weergegeven. Sleep de automatische greep naar andere cellen om ook daar de resultaten te krijgen.
Klik op Datum en Tijd Assistentom meer te weten te komen over het gebruik van deze functie.
Klik op Kutools voor Excelom alle functies van deze invoegtoepassing te ontdekken.
Klik op Gratis downloadenom een gratis 30-daagse proefversie van Kutools voor Excel te krijgen.
2,41 Controleren of markeren of een datum verlopen is
Als u een lijst hebt met Vervaldatum van producten, wilt u mogelijk de data controleren en markeren die verlopen zijn ten opzichte van vandaag, zoals in de onderstaande schermafbeelding wordt getoond.
Eigenlijk kunt u de functie Voorwaardelijke opmaak gebruiken om deze taak snel uit te voeren.
1. Selecteer de datums die u wilt controleren en klik vervolgens op Start > Voorwaardelijke opmaak gebruiken > Nieuwe regel.
2. In het dialoogvenster Nieuwe opmaakregel selecteert u Een formule gebruiken om te bepalen welke cellen worden opgemaakt in het gedeelte Regeltype selecteren en typt u =B2<VANDAAG() in het invoervak (B2 is de eerste datum die u wilt controleren). Klik vervolgens op Opmaak om het dialoogvenster Celopmaak instellen te openen en kies een duidelijke opmaak om de vervaldatum te markeren, zoals gewenst. Klik op OK > OK.
2,42 Laatste dag van de huidige maand/eerste dag van Volgende maand ophalen
De Vervaldatum van sommige producten valt op de laatste dag van de productiemaand of op de eerste dag van de Volgende maand van productie. Volg dit gedeelte om snel de Vervaldatum te genereren op basis van de productiedatum.
Laatste dag van de huidige maand ophalen
Er staat een productiedatum in cel B13. Gebruik de volgende formule:
=EOMONTH(B13,0)
Druk op de Enter-toets om het resultaat te verkrijgen.
Eerste dag van Volgende maand ophalen
Er staat een productiedatum in cel B18. Gebruik de volgende formule:
=EOMONTH(B18,0)+1
Druk op de Enter-toets om het resultaat te verkrijgen.
3. Leeftijd berekenen
In dit gedeelte worden methoden beschreven om de leeftijd te berekenen op basis van een opgegeven datum of serienummer.
3,11 Leeftijd berekenen op basis van gegeven geboortedatum
Leeftijd als decimaal getal ophalen op basis van geboortedatum
Klik op JAAR.DEEL voor meer informatie over de argumenten en het gebruik.
Als u bijvoorbeeld de leeftijden wilt berekenen op basis van de lijst met geboortedata in kolom B2:B9, gebruikt u de volgende formule:
=YEARFRAC(B2,TODAY())
Druk op Enter en sleep de automatische vulgreep omlaag totdat alle leeftijden zijn berekend.
Tip:
1) U kunt het aantal decimalen naar wens instellen in het dialoogvenster Celopmaak instellen.
2) Als u de leeftijd op een specifieke datum wilt berekenen op basis van een gegeven geboortedatum, vervangt u VANDAAG() door de specifieke datum tussen dubbele aanhalingstekens, zoals =YEARFRAC(B2,„[[PH_324]]")
3) Als u de leeftijd voor volgend jaar wilt berekenen op basis van de geboortedatum, voegt u eenvoudig 1 toe aan de formule, bijvoorbeeld =JAAR.DEEL(B2;VANDAAG())+1.
Leeftijd als geheel getal ophalen op basis van geboortedatum
Klik op DATUMVERSCHIL voor meer informatie over de argumenten en het gebruik.
Met behulp van het bovenstaande voorbeeld kunt u de leeftijd berekenen op basis van de geboortedata in de lijst in B2:B9 met de volgende formule:
=DATEDIF(B2,TODAY(),„y")
Druk op Enter en sleep de automatische vulgreep omlaag totdat alle leeftijden zijn berekend.
Tip:
1) Als u de leeftijd op een specifieke datum wilt berekenen op basis van een gegeven geboortedatum, vervangt u VANDAAG() door die specifieke datum tussen dubbele aanhalingstekens, bijvoorbeeld =DATEDIF(B2,"[[PH_329]]",„y").
2) Als u de leeftijd van volgend jaar wilt berekenen op basis van de geboortedatum, voegt u eenvoudig 1 toe aan de formule, zoals =DATEDIF(B2,TODAY(),„y")+1.
3,12 Leeftijd berekenen in jaren, maanden en dagen op basis van geboortedatum
Als u de leeftijd wilt berekenen op basis van een gegeven geboortedatum en het resultaat wilt weergeven als xx jaar, xx maanden, xx dagen (zoals in de onderstaande schermafbeelding), dan helpt de volgende lange formule u daarbij.
Gebruik de volgende formule om de leeftijd in jaren, maanden en dagen te berekenen op basis van de geboortedatum in cel B12:
=DATEDIF(B12,TODAY(),"Y")&„ Years, "&DATEDIF(B12,TODAY(),"YM")&„ Months, "&DATEDIF(B12,TODAY(),"MD")&„ Days"
Druk op Enter om de leeftijd te verkrijgen en sleep de automatische vulgreep naar andere cellen.
Tip:
Als u de leeftijd op een specifieke datum wilt berekenen op basis van een gegeven geboortedatum, vervangt u VANDAAG() door die specifieke datum tussen dubbele aanhalingstekens, zoals in: =DATEDIF(B12,"[[PH_337]]","Y")&„ jaar, "&DATUMVERSCHIL(B12,"1/1/2021","YM")&„ maanden, "&DATUMVERSCHIL(B12,"1/1/2021","MD")&„ dagen".
3,13 Leeftijd berekenen op basis van geboortedatum vóór 1/1/1900
In Excel kan een datum vóór 1/1/1900 niet als datum/tijd worden ingevoerd of correct worden berekend. Als u echter de leeftijd van een beroemd persoon wilt berekenen op basis van de geboortedatum (vóór 1/11900) en de sterfdatum, dan kan alleen een VBA-code u daarbij 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 nieuwe module te maken.
2. Kopieer de onderstaande code en plak deze vervolgens in de nieuwe module.
VBA: Leeftijd berekenen vóór 1/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, keer terug naar het werkblad en selecteer een cel waarin u de berekende leeftijd wilt plaatsen. Typ =AgeFunc(geboortedatum;sterfdatum) — in dit geval =AgeFunc(B22;C22) — en druk op Enter om de leeftijd te verkrijgen. Gebruik indien nodig de automatische vulgreep om deze formule op andere cellen toe te passen.
Als u Kutools voor Excel hebt geïnstalleerd in Excel, kunt u de functie Datum en Tijd Assistent gebruiken om de leeftijd te berekenen.
1. Selecteer een cel waarin u de berekende leeftijd wilt plaatsen en klik op Kutools > Formulehulp > Datum en Tijd Assistent.
2. In het dialoogvenster Datum en Tijd Assistent:
- 1) Vink het Leeftijd-optievakje aan;
- 2) Kies de geboortedatumcel of voer direct de geboortedatum in, of klik op het kalenderpictogram om de geboortedatum te selecteren;
- 3) Kies de Vandaag-optie als u de huidige leeftijd wilt berekenen; kies de Specifieke datum-optie en voer de datum in als u de leeftijd in het verleden of de toekomst wilt berekenen;
- 4) Geef het uitvoertype op via de Keuzelijst;
- 5) Bekijk een voorbeeld van het resultaat. Klik op OK.
Klik op Datum en Tijd Assistentom meer te weten te komen over het gebruik van deze functie.
Klik op Kutools voor Excelom alle functies van deze invoegtoepassing te ontdekken.
Klik op Gratis downloadenom een gratis 30-daagse proefversie van Kutools voor Excel te krijgen.
3,31 Haal geboortedatum uit ID-nummer
Als u een lijst met ID-nummers hebt waarvan de eerste 6 cijfers de geboortedatum aanduiden – bijvoorbeeld 920315330, wat overeenkomt met 15 maart 1992 – hoe kunt u die geboortedatum dan snel in een aparte kolom plaatsen?
Laten we nu de lijst met ID-nummers die begint in cel C2 als voorbeeld nemen en de volgende formule gebruiken:
=MID(C2,5,2)&„/"&MID(C2,3,2)&„/"&MID(C2,1,2)
Druk op Enter en sleep vervolgens de automatische vulgreep omlaag om de overige resultaten te verkrijgen.
Opmerking:
U kunt de verwijzing in de formule aanpassen aan uw specifieke behoeften. Stel dat het ID-nummer er als volgt uitziet: 13219920420392, met geboortedatum 04/20/1992 — dan past u de formule aan 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 een lijst met ID-nummers de geboortedatum bevat in de eerste 6 cijfers – bijvoorbeeld 920315330, wat overeenkomt met geboortedatum 15-03-1992 – hoe berekent u dan snel de leeftijd op basis van elk ID-nummer in Excel?
Laten we nu de lijst met ID-nummers die begint in cel C2 als voorbeeld nemen en de volgende formule gebruiken:
=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),„y")
Druk op Enter en sleep vervolgens de automatische vulgreep omlaag om de overige resultaten te verkrijgen.
Opmerking:
In deze formule wordt, als het jaartal kleiner is dan het huidige jaar, het jaartal beschouwd als beginnend met 20; bijvoorbeeld 200203943 wordt beschouwd als het jaar 2020. Als het jaartal groter is dan het huidige jaar, wordt het jaartal beschouwd als beginnend met 19; bijvoorbeeld 920420392 wordt beschouwd als het jaar 1992.
Meer Excel-tutorials:
Combineer meerdere werkmappen en werkbladen tot één bestand
Deze handleiding behandelt vrijwel alle combinatiescenario’s die u kunt tegenkomen en biedt professionele oplossingen.
Splits tekst-, getal- en datumcellen (scheid in meerdere kolommen)
Deze handleiding is onderverdeeld in drie delen: het splitsen van tekstcellen, getalcellen en datumcellen. Elk deel bevat praktische voorbeelden om u te helpen deze splitsproblemen eenvoudig op te lossen.
Combineer inhoud van meerdere cellen zonder gegevensverlies in Excel
Deze handleiding richt zich op het extraheren van tekst of getallen op een specifieke positie binnen een cel en biedt verschillende methoden om u te helpen gegevens uit een cel op te halen op basis van die positie in Excel.
Vergelijk twee kolommen op overeenkomsten en verschillen in Excel
Dit artikel behandelt vrijwel alle mogelijke scenario’s bij het vergelijken van twee kolommen en helpt u daarbij verder.
De beste kantoorproductiviteitstools
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); Leeslay-out (lees en bewerk gemakkelijk grote aantallen cellen); Plakken naar Filterbereik...
- Samengevoegde cellen/rijen/kolommen en bijbehorende gegevens behouden; inhoud van cellen splitsen; Dubbele rijen combineren en som/gemiddelde berekenen... Voorkom dubbele invoer in cellen; Bereiken vergelijken...
- Selecteer dubbele of unieke rijen; selecteer lege rijen (alle cellen zijn leeg); superzoeken en fuzzy zoeken in meerdere werkmappen; willekeurig selecteren...
- Exacte kopie van meerdere cellen zonder dat u de formuleverwijzing hoeft te wijzigen; automatisch verwijzingen maken naar meerdere werkbladen; opsommingstekens invoegen, selectievakjes en meer...
- Favoriete formules, berekeningen, grafieken en afbeeldingen snel invoegen;Cellen met wachtwoord versleutelen;Mailinglijst maken en e-mails verzenden...
- Tekst extraheren, tekst toevoegen, tekens verwijderen op een bepaalde positie, spaties verwijderen; gegevensbladstatistieken maken en afdrukken; converteren tussen celinhoud en opmerkingen...
- Superfilter (sla filterinstellingen op en pas ze toe op andere werkbladen); Geavanceerd sorteren per maand, week, dag, frequentie en meer; Speciaal filter op vet, cursief...
- Combineer werkmappen en werkbladen; voeg tabellen samen op basis van een sleutelkolom; splits gegevens op in meerdere werkbladen; batchconverteer xls-, xlsx- en PDF-bestanden...
- Groepeer draaitabel op weeknummer, dag van de week en meer...Toon niet-vergrendelde en vergrendelde selecties met verschillende kleuren;Markeer cellen met formules of namen...
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
