Excel-zelfstudie: DateTime-berekening (verschil berekenen, leeftijd, optellen / aftrekken)
In Excel wordt de datum-tijdberekening vaak gebruikt, zoals het berekenen van het verschil tussen twee datums/tijden, het optellen of aftrekken van datetime, het bepalen van de leeftijd op basis van de geboortedatum enzovoort. Hier in deze tutorial somt het bijna scenario's op over datetime-berekening en biedt het de gerelateerde methoden voor u.
In deze tutorial maak ik enkele voorbeelden om de methoden uit te leggen, je kunt de referenties wijzigen die je nodig hebt wanneer je onderstaande VBA-code of formules gebruikt
1. Bereken het verschil tussen twee datums/tijden
Het berekenen van het verschil tussen twee datums of twee tijden is misschien wel het meest normale probleem van datetime-berekening dat u tegenkomt in het dagelijkse Excel-werk. De onderstaande voorbeelden kunnen u helpen om de efficiëntie te verbeteren wanneer u dezelfde problemen tegenkomt.
1.11 Bereken het verschil tussen twee datums in dagen/maanden/jaren/weken
Excel DATEDIF-functie kan worden gebruikt om snel het verschil tussen twee datums in dagen, maanden, jaren en weken te berekenen.
Klik voor meer details over DATUM.IF functie
Dagen verschil tussen twee datums
Gebruik de formule als volgt om het verschil in dagen tussen twee datums in cel A2 en B2 te krijgen:
=DATEDIF(A2,B2,"d")
Media Enter sleutel om het resultaat te krijgen.
Maanden verschil tussen twee datums
Gebruik de formule als volgt om het maandverschil tussen twee datums in cel A5 en B5 te krijgen:
=DATEDIF(A5,B5,"m")
Media Enter sleutel om het resultaat te krijgen.
Jaar verschil tussen twee datums
Gebruik de formule als volgt om het jaarverschil tussen twee datums in cel A8 en B8 te krijgen:
=DATEDIF(A8,B8,"y")
Media Enter sleutel om het resultaat te krijgen.
Weken verschil tussen twee datums
Gebruik de formule als volgt om het verschil van weken tussen twee datums in cel A11 en B11 te krijgen:
=DATEDIF(A11,B11,"d")/7
Media Enter sleutel om het resultaat te krijgen.
Opmerking:
1) Wanneer u de bovenstaande formule gebruikt om het verschil in weken te krijgen, kan dit een resultaat in datumnotatie retourneren, u moet het resultaat naar behoefte opmaken in algemeen of getal.
2) Wanneer u de bovenstaande formule gebruikt om het verschil in weken te krijgen, kan het terugkeren naar een decimaal getal, als u het gehele weeknummer wilt krijgen, kunt u de functie ROUNDDOWN eerder toevoegen, zoals hieronder wordt getoond om het gehele weekverschil te krijgen:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Bereken maanden, negeer jaren en dagen tussen twee datums
Als u alleen het maandverschil wilt berekenen en jaren en dagen tussen twee datums negeert, zoals de onderstaande schermafbeelding laat zien, is hier een formule die u kan helpen.
=DATEDIF(A2,B2,"ym")
Media Enter sleutel om het resultaat te krijgen.
A2 is de startdatum en B2 is de einddatum.
1.13 Bereken dagen negeer jaren en maanden tussen twee datums
Als u alleen het verschil in dagen wilt berekenen, waarbij u jaren en maanden tussen twee datums negeert, zoals hieronder afgebeeld, is hier een formule die u kan helpen.
=DATEDIF(A5,B5,"md")
Media Enter sleutel om het resultaat te krijgen.
A5 is de startdatum en B5 is de einddatum.
1.14 Bereken het verschil tussen twee datums en rendementsjaren, maanden en dagen
Als u het verschil tussen twee datums wilt krijgen en xx jaar, xx maanden en xx dagen wilt retourneren, zoals de onderstaande schermafbeelding laat zien, is hier ook een formule beschikbaar.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
Media Enter sleutel om het resultaat te krijgen.
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, wijzigt u de einddatum in de bovenstaande formules in TODAY(). Hier kunt u het verschil van dagen tussen een datum in het verleden en vandaag als instantie berekenen.
=DATEDIF(A11,TODAY(),"d")
Media Enter sleutel om het resultaat te krijgen.
Note: als u het verschil tussen een toekomstige datum en vandaag wilt berekenen, wijzigt u de startdatum in vandaag en neemt u de toekomstige datum als einddatum als volgt:
=DATEDIF(TODAY(),A14,"d")
Merk op dat de startdatum kleiner moet zijn dan de einddatum in de DATEDIF-functie, anders keert het terug naar #GETAL! fout waarde.
1.16 Bereken werkdagen met of zonder vakantie tussen twee datums
Soms moet u het aantal werkdagen met of zonder feestdagen tussen twee opgegeven datums tellen.
In dit deel gebruikt u de functie NETWORKDAYS.INTL:
Klik NETWERKDAGEN.INTL om de argumenten en het gebruik ervan te kennen.
Tel werkdagen met feestdagen
Gebruik de formule als volgt om de werkdagen met vakantie tussen twee datums in cel A2 en B2 te tellen:
=NETWORKDAYS.INTL(A2,B2)
Media Enter sleutel om het resultaat te krijgen.
Tel werkdagen zonder feestdagen
Gebruik de formule als volgt om de werkdagen te tellen met feestdagen tussen twee datums in cel A2 en B2, en exclusief de feestdagen in het bereik D5:D9:
=NETWERKDAGEN.INTL(A5,B5,1,D5:D9)
Media Enter sleutel om het resultaat te krijgen.
Opmerking:
In de bovenstaande formules nemen ze zaterdag en zondag als weekend, als je verschillende weekenddagen hebt, verander dan het argument [weekend] naar behoefte.
1.17 Bereken weekenden tussen twee datums
Als u het aantal weekenden tussen twee datums wilt tellen, kunnen de functies SOMPRODUCT of SOM u een plezier doen.
Om de weekenden (zaterdag en zondag) tussen twee datums in cel A12 en B12 te tellen:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Media Enter sleutel om het resultaat te krijgen.
1.18 Bereken specifieke weekdag tussen twee datums
Om het nummer van een specifieke weekdag zoals maandag tussen twee datums te tellen, kan de combinatie van INT- en WEEKDAY-functies u helpen.
Cel A15 en B15 zijn de twee datums waar je maandag tussen wilt tellen, gebruik de formule als volgt:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Media Enter sleutel om het resultaat te krijgen.
Wijzig het nummer van de weekdag in de WEEKDAY-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 misschien de resterende dagen in de maand of het jaar weten op basis van de opgegeven datum, zoals de onderstaande schermafbeelding laat zien:
Resterende dagen van de huidige maand ophalen
Klik EOMAAND om het argument en het gebruik te kennen.
Gebruik de formule als volgt om de resterende dagen van de huidige maand in cel A2 te krijgen:
=EOMONTH(A2,0)-A2
Media Enter toets en sleep de handgreep voor automatisch vullen om deze formule indien nodig op andere cellen toe te passen.
Tip: de resultaten kunnen worden weergegeven als datumformaat, verander ze gewoon als algemeen of getalsformaat.
Resterende dagen in het huidige jaar ophalen
Gebruik de formule als volgt om de resterende dagen van het huidige jaar in cel A2 te krijgen:
=DATE(YEAR(A2),12,31)-A2
Media Enter toets en sleep de handgreep voor automatisch vullen om deze formule indien nodig op andere cellen toe te passen.
1.21 Bereken het verschil tussen twee tijden
Om het verschil tussen twee tijden te krijgen, zijn hier twee eenvoudige formules die u kunnen helpen.
Stel dat in cel A2 en B2 start_time en end_time afzonderlijk bevatten, met de volgende formules:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Media Enter sleutel om het resultaat te krijgen.
Opmerking:
- Als u end_time-start_time gebruikt, kunt u het resultaat naar behoefte opmaken in een andere tijdnotatie in het dialoogvenster Cellen opmaken.
- Als u TEXT(end_time-first_time,"time_format") gebruikt, voert u de tijdnotatie in waarvan u wilt dat het resultaat in de formule wordt weergegeven, zoals TEXT(end_time-first_time,"h") retourneert 16.
- Als de eindtijd kleiner is dan begintijd, retourneren beide formules foutwaarden. Om dit probleem op te lossen, kunt u ABS vooraan deze formules toevoegen, zoals ABS(B2-A2), ABS(TEXT(B2-A2,"uu:mm:ss")), en het resultaat vervolgens opmaken als tijd.
1.22 Bereken het verschil tussen twee tijden in uren/minuten/seconden
Als u het verschil tussen twee tijden in uren, minuten of seconden wilt berekenen, zoals de onderstaande schermafbeelding laat zien, volg dan dit gedeelte.
Krijg uur verschil tussen twee tijden
Gebruik de formule als volgt om het uurverschil tussen twee tijden in A5 en B5 te krijgen:
=INT((B5-A5)*24)
Media Enter toets en formatteer vervolgens het resultaat van de tijdnotatie als algemeen of getal.
Als u het verschil in decimale uren wilt krijgen, gebruikt u (end_time-start_time)*24.
Krijg minuten verschil tussen twee tijden
Gebruik de formule als volgt om het verschil in minuten tussen twee tijden in A8 en B8 te krijgen:
=INT((B8-A8)*1440)
Media Enter toets en formatteer vervolgens het resultaat van de tijdnotatie als algemeen of getal.
Als u het verschil in decimale minuten wilt krijgen, gebruikt u (end_time-start_time)*1440.
Krijg seconden verschil tussen twee tijden
Gebruik de formule als volgt om het verschil in seconden tussen twee tijden in A5 en B5 te krijgen:
=(B11-A11)*86400)
Media Enter toets en formatteer vervolgens het resultaat van de tijdnotatie als algemeen of getal.
1.23 Bereken urenverschil alleen tussen twee tijden (niet meer dan 24 uur)
Als het verschil tussen twee tijden niet meer dan 24 uur is, kan de UUR-functie snel het uurverschil tussen deze twee tijden krijgen.
Klik HOUR voor meer details over deze functie.
Gebruik de functie HOUR als volgt om het uurverschil tussen de tijden in cel A14 en B14 te krijgen:
=HOUR(B14-A14)
Media Enter sleutel om het resultaat te krijgen.
De start_time moet kleiner zijn dan de end_time, anders retourneert de formule #GETAL! fout waarde.
1.24 Bereken het verschil in minuten alleen tussen twee tijden (niet meer dan 60 minuten)
De MINUTE-functie kan snel het enige minutenverschil tussen deze twee tijden krijgen en uren en seconden negeren.
Klik MINUUT voor meer details over deze functie.
Gebruik de MINUTE-functie als volgt om alleen het minutenverschil tussen de tijden in cel A17 en B17 te krijgen:
=MINUTE(B17-A17)
Media Enter sleutel om het resultaat te krijgen.
De start_time moet kleiner zijn dan de end_time, anders retourneert de formule #GETAL! fout waarde.
1.25 Bereken seconden verschil alleen tussen twee tijden (niet meer dan 60 seconden)
De SECOND-functie kan snel het verschil van enige seconden tussen deze twee tijden krijgen en uren en minuten negeren.
Klik TWEEDE voor meer details over deze functie.
Om alleen het verschil in seconden tussen de tijden in cel A20 en B20 te krijgen, gebruikt u de SECOND-functie als volgt:
=SECOND(B20-A20)
Media Enter sleutel om het resultaat te krijgen.
De start_time moet kleiner zijn dan de end_time, anders retourneert de formule #GETAL! fout waarde.
1.26 Bereken het verschil tussen twee tijden en retoururen, minuten, seconden
Als u het verschil tussen twee tijden wilt weergeven als xx uur xx minuten xx seconden, gebruik dan de TEKST-functie zoals hieronder weergegeven:
Klik TEKST om de argumenten en het gebruik van deze functie te realiseren.
Gebruik de formule als volgt om het verschil tussen tijden in cel A23 en B23 te berekenen:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Media Enter sleutel om het resultaat te krijgen.
Opmerking:
Deze formule berekent ook alleen het uurverschil van maximaal 24 uur, en de eindtijd moet groter zijn dan begintijd, anders wordt #WAARDE geretourneerd! fout waarde.
1.27 Bereken het verschil tussen twee datetimes
Als er twee tijden zijn in de notatie mm/dd/jjjj uu:mm:ss, om het verschil ertussen te berekenen, kunt u een van de onderstaande formules gebruiken als u dat wilt.
Krijg tijdsverschil tussen twee datums en retourneer resultaat in uu:mm:ss formaat
Neem twee datumtijden in cel A2 en B2 als instantie, gebruik de formule als volgt:
=B2-A2
Media Enter key, retourneert een resultaat in datetime-formaat en formatteer dit resultaat als [H]: mm: ss in de aangepaste categorie onder de Telefoon Nummer tab in Cellen opmaken dialoogvenster.
Krijg het verschil tussen twee datetimes en retourdagen, uren, minuten, seconden
Neem twee datumtijden in cel A5 en B5 als instantie, gebruik de formule als volgt:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Media Enter sleutel om het resultaat te krijgen.
Opmerking: in beide formules moet end_datetime groter zijn dan start_datetime, anders retourneren de formules foutwaarden.
1.28 Bereken tijdsverschil met milliseconden
Ten eerste moet u weten hoe u de cel moet opmaken om de milliseconden weer te geven:
Selecteer de cellen die u in milliseconden wilt weergeven en rechts om te selecteren Cellen opmaken om de Cellen opmaken dialoogvenster, selecteer Eigen in de Categorie lijst onder het tabblad Nummer en typ dit uu: mm: ss.000 in het tekstvak.
Gebruik formule:
Hier om het verschil tussen twee tijden in cel A8 en B8 te berekenen, gebruikt u de formule als:
=ABS(B8-A8)
Media Enter sleutel om het resultaat te krijgen.
1.29 Bereken werkuren tussen twee data exclusief weekends
Soms moet u de werkuren tussen twee datums tellen, met uitzondering van weekends (zaterdag en zondag).
Hier zijn de werkuren vastgesteld op 8 uur per dag en om de werkuren te berekenen tussen twee datums in cel A16 en B16, gebruikt u de formule als volgt:
=NETWORKDAYS(A16,B16) * 8
Media Enter toets en maak het resultaat vervolgens op als algemeen of getal.
Ga voor meer voorbeelden over het berekenen van werkuren tussen twee datums naar Krijg werkuren tussen twee datums in Excel
Als je Kutools for Excel geïnstalleerd in Excel, kan 90 procent van de berekeningen van het datum-tijdverschil snel worden opgelost zonder formules te onthouden.
1.31 Bereken het verschil tussen twee datetimes door Data & Time Helper
Om het verschil tussen twee datetimes in Excel te berekenen, hoeft u alleen de Helper voor datum en tijd is genoeg.
1. Selecteer een cel waarin u het berekende resultaat plaatst en klik op Kutools > Formule Helper > Helper voor datum en tijd.
2. In het knallen Helper voor datum en tijd dialoogvenster, volg dan de onderstaande instellingen:
- Check Verschil keuze;
- Selecteer de start datetime en eind datetime in Argumenten ingevoerd sectie, kunt u datetime ook rechtstreeks handmatig invoeren in het invoervak, of op het kalenderpictogram klikken om de datum te selecteren;
- Selecteer het type uitvoerresultaat in de vervolgkeuzelijst;
- Bekijk een voorbeeld van het resultaat in Resultaat pagina.
3. klikken Ok. Het berekende resultaat wordt uitgevoerd en sleep de greep voor automatisch aanvullen over de cellen die u ook moet berekenen.
Tip:
Als u het verschil tussen twee datetimes wilt zien en het resultaat wilt weergeven als dagen, uren en minuten met Kutools voor Excel, doe dan als volgt:
Selecteer een cel waar u het resultaat wilt plaatsen en klik op Kutools > Formule Helper > Datum en tijd > Tel dagen, uren en minuten tussen twee datums.
Dan in de Formules Helper dialoogvenster, geef de startdatum en einddatum op en klik vervolgens op Ok.
En het verschilresultaat wordt weergegeven als dagen, uren en minuten.
Klik Helper voor datum en tijd om meer te weten te komen over het gebruik van deze functie.
Klik Kutools for Excel om alle functies van deze invoegtoepassing te kennen.
Klik Gratis download om een gratis proefperiode van 30 dagen van Kutools voor Excel te krijgen
1.32 Bereken weekend/werkdag/specifieke weekdag verschil tussen twee datums door Formula Helper
Als u snel het weekend, werkdagen of een specifieke weekdag tussen twee datums wilt tellen, Kutools voor Excel's Formule Helper groep kan je helpen.
1. Selecteer de cel die het berekende resultaat zal plaatsen, klik op Kutools > Statistisch > Aantal niet-werkdagen tussen twee datums/Aantal werkdagen tussen twee datums/Tel het aantal specifieke dag van de week.
2. In de pop-out Formules Helper dialoogvenster, specificeer de startdatum en einddatum, als u zich aanmeldt Tel het aantal specifieke dag van de week, moet u ook de weekdag opgeven.
Om de specifieke weekdag te tellen, kunt u de notitie raadplegen om 1-7 te gebruiken om zondag-zaterdag aan te geven.
3. klikken Oken sleep indien nodig de handgreep voor automatisch aanvullen over cellen die het aantal weekenden/werkdagen/specifieke weekdagen moeten tellen.
Klik Kutools for Excel om alle functies van deze invoegtoepassing te kennen.
Klik Gratis download om een gratis proefperiode van 30 dagen van Kutools voor Excel te krijgen
2. Datum en tijd optellen of aftrekken
Behalve voor het berekenen van het verschil tussen twee datetimes, is optellen of aftrekken ook de normale datetime-berekening in Excel. U wilt bijvoorbeeld de vervaldatum krijgen op basis van de productiedatum en het aantal houdbaarheidsdagen voor een product.
2.11 Dagen bij een datum optellen of aftrekken
Er zijn twee verschillende methoden om een bepaald aantal dagen bij een datum op te tellen of af te trekken.
Stel dat u 21 dagen toevoegt aan een datum in cel A2, kies dan een van de onderstaande methoden om dit op te lossen,
Methode 1 datum+dagen
Selecteer een cel en typ de formule:
=A+21
Media Enter sleutel om het resultaat te krijgen.
Als u 21 dagen wilt aftrekken, verandert het plusteken (+) in het minteken (-).
Methode 2 Plakken speciaal
1. Typ het aantal dagen dat u in een cel wilt toevoegen, bijvoorbeeld in cel C2, en druk vervolgens op Ctrl + C om het te kopiëren.
2. Selecteer vervolgens de datums die u 21 dagen wilt toevoegen, klik met de rechtermuisknop om het contextmenu weer te geven en selecteer Plakken speciaal....
3. In de plakken speciaal dialoogvenster, vink aan Toevoegen optie (Als u dagen wilt aftrekken, vink dan aan Aftrekken keuze). Klik OK.
4. Nu veranderen de originele datums in 5-cijferige getallen, formatteer ze als datums.
2.12 Maanden optellen of aftrekken bij een datum
Om maanden bij een datum op te tellen of af te trekken, kan de EDATE-functie worden gebruikt.
Klik BEWERKEN om de argumenten en het gebruik ervan te bestuderen.
Stel dat u 6 maanden toevoegt aan de datum in cel A2, gebruik de formule als volgt:
=EDATE(A2,6)
Media Enter sleutel om het resultaat te krijgen.
Als u 6 maanden van de datum wilt aftrekken, wijzigt u 6 in -6.
2.13 Jaren optellen of aftrekken bij een datum
Om n jaar bij een datum op te tellen of af te trekken, kan een formule worden gebruikt die de functies DATUM, JAAR, MAAND en DAG combineert.
Stel dat u 3 jaar toevoegt aan de datum in cel A2, gebruik de formule als volgt:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Media Enter sleutel om het resultaat te krijgen.
Als u 3 jaar van de datum wilt aftrekken, wijzigt u 3 in -3.
2.14 Optellen of aftrekken van weken bij een datum
Om weken bij een datum op te tellen of af te trekken, is de algemene formule:
Stel dat u 4 weken zou toevoegen aan de datum in cel A2, gebruik de formule als volgt:
=A2+4*7
Media Enter sleutel om het resultaat te krijgen.
Als u 4 weken van de datum wilt aftrekken, wijzigt u het plusteken (+) in het minteken (-).
2.15 Werkdagen optellen of aftrekken inclusief of exclusief feestdagen
In deze sectie wordt uitgelegd hoe u de functie WERKDAG kunt gebruiken om werkdagen toe te voegen aan of af te trekken van een bepaalde datum, exclusief feestdagen of inclusief feestdagen.
Bezoek WERKDAG om meer details te weten over de argumenten en het gebruik ervan.
Werkdagen inclusief feestdagen toevoegen
In cel A2 staat de datum die u gebruikt, in cel B2 staat het aantal dagen dat u wilt toevoegen, gebruik de formule als volgt:
=WORKDAY(A2,B2)
Media Enter sleutel om het resultaat te krijgen.
Werkdagen toevoegen exclusief feestdagen
In cel A5 is de datum die u gebruikt, in cel B5 staat het aantal dagen dat u wilt toevoegen, in het bereik D5:D8 staan de feestdagen vermeld, gebruik de formule als volgt:
=WORKDAY(A5,B5,D5:D8)
Media Enter sleutel om het resultaat te krijgen.
Opmerking:
De functie WERKDAG neemt zaterdag en zondag als weekends in, als uw weekenden op zaterdag en zondag vallen, kunt u de functie WOKRDAY.INTL toepassen, die het specificeren van weekends ondersteunt.
Bezoek WERKDAG.INTL voor meer details.
Als u werkdagen van een datum wilt aftrekken, wijzigt u gewoon het aantal dagen in negatief in de formule.
2.16 Optellen of aftrekken van een bepaald jaar, maand, dagen tot een datum
Als u een specifiek jaar, maanddagen aan een datum wilt toevoegen, kan de formule die de functie DATUM, JAAR, MAAND en DAGEN combineert, u een plezier doen.
Om 1 jaar 2 maanden en 30 dagen toe te voegen aan een datum in A11, gebruikt u de formule als volgt:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Media Enter sleutel om het resultaat te krijgen.
Als u wilt aftrekken, wijzigt u alle plustekens (+) in mintekens (-).
2.21 Uren/minuten/seconden optellen of aftrekken bij een datetime
Hier vindt u enkele formules voor het optellen of aftrekken van uren, minuten of seconden bij een datetime.
Uren optellen of aftrekken bij een datetime
Stel dat u 3 uur toevoegt aan een datumtijd (kan ook een tijd zijn) in cel A2, gebruik dan de formule als volgt:
=A2+3/24
Media Enter sleutel om het resultaat te krijgen.
Uren optellen of aftrekken bij een datetime
Stel dat u 15 minuten toevoegt aan een datetime (kan ook een tijd zijn) in cel A5, gebruik dan de formule als volgt:
=A2+15/1440
Media Enter sleutel om het resultaat te krijgen.
Uren optellen of aftrekken bij een datetime
Stel dat u 20 seconden toevoegt aan een datetime (kan ook een tijd zijn) in cel A8, gebruik dan de formule als volgt:
=A2+20/86400
Media Enter sleutel om het resultaat te krijgen.
Stel dat er een Excel-tabel is die de werktijd van alle medewerkers in een week registreert, om de totale werktijd op te tellen voor het berekenen van de betalingen, kunt u gebruiken SOM(bereik) om het resultaat te krijgen. Maar over het algemeen wordt het opgetelde resultaat weergegeven als een tijd van maximaal 24 uur, zoals de onderstaande schermafbeelding laat zien. Hoe kunt u het juiste resultaat krijgen?
Eigenlijk hoeft u het resultaat alleen maar op te maken als [uu]:mm:ss.
Klik met de rechtermuisknop op de resultaatcel, kies Cellen opmaken in het contextmenu en in de popping Cellen opmaken dialoogvenster, kies Eigen uit de blist, en typ [uu]:mm:ss in het tekstvak in het rechtergedeelte, klik op OK.
Het opgetelde resultaat wordt correct weergegeven.
2.23 Werkuren toevoegen aan een datum exclusief weekend en vakantie
Hier vindt u een lange formule voor het verkrijgen van de einddatum op basis van het toevoegen van een specifiek aantal werkuren aan een startdatum en exclusief weekends (zaterdag en zondag) en feestdagen.
In een Excel-tabel bevat A11 de startdatumtijd en bevat B11 de werkuren, in cel E11 en E13 de begin- en eindtijden van het werk en cel E15 bevat de vakantie die wordt uitgesloten.
Gebruik de formule als volgt:
=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))
Media Enter sleutel om het resultaat te krijgen.
Als je Kutools for Excel hebt geïnstalleerd, is er maar één tool: Datum & tijd hulpr kan de meeste berekeningen voor het optellen en aftrekken van datetime oplossen.
1. Klik op een cel waarvan u het resultaat wilt weergeven en pas deze tool toe door te klikken op Kutools > Formule Helper > Datum & Tijd Helper.
2. In de Helper voor datum en tijd dialoogvenster, vink aan Toevoegen optie of Aftrekken optie als je nodig hebt, kies dan de cel of typ direct de datetime die je wilt gebruiken in Argumenten ingevoerd sectie, specificeer vervolgens de jaren, maanden, weken, dagen, uren, minuten en seconden die u wilt optellen of aftrekken en klik vervolgens op Ok. Zie screenshot:
U kunt een voorbeeld van het berekende resultaat bekijken in de Resultaat pagina.
Nu wordt het resultaat uitgevoerd, sleep de automatische handgreep over andere cellen om de resultaten te krijgen.
Klik Helper voor datum en tijd om meer te weten te komen over het gebruik van deze functie.
Klik Kutools for Excel om alle functies van deze invoegtoepassing te kennen.
Klik Kosten downloaden om een gratis proefperiode van 30 dagen van Kutools voor Excel te krijgen
2.41 Controleer of markeer of een datum is verlopen
Als er een lijst met vervaldatums van producten is, wilt u misschien de datums controleren en markeren die zijn verlopen op basis van vandaag, zoals de onderstaande schermafbeelding laat zien.
Eigenlijk, de Conditionele opmaak kan deze klus snel aan.
1. Selecteer de datums die u wilt controleren en klik vervolgens op Home > Conditionele opmaak > Nieuwe regel.
2. In de Nieuwe opmaakregel dialoogvenster, selecteer Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt in de Selecteer een regeltype sectie en typ =B2 in het invoerveld (B2 is de eerste datum die u wilt controleren), en klik op Formaat te voorschijn komen Cellen opmaken dialoogvenster en kies vervolgens indien nodig een andere opmaak om de vervaldatums te overtreffen. Klik OK > OK.
2.42 Einde van huidige maand/eerste dag van volgende maand/a>
De vervaldatums van sommige producten zijn aan het einde van de productiemaand of de eerste dag van de volgende productiemaand, voor een snelle lijst van de vervaldatums op basis van de productiedatum, volg dit deel.
Ontvang het einde van de huidige maand
Hier is een productiedatum in cel B13, gebruik de formule als volgt:
=EOMONTH(B13,0)
Media Enter sleutel om het resultaat te krijgen.
Ontvang 1e dag van volgende maand
Hier is een productiedatum in cel B18, gebruik de formule als volgt:
=EOMONTH(B18,0)+1
Media Enter sleutel om het resultaat te krijgen.
3. Bereken leeftijd
In deze sectie somt het de methoden op voor het oplossen van het berekenen van de leeftijd op basis van een bepaalde datum of een serienummer.
3.11 Bereken leeftijd op basis van gegeven geboortedatum
Krijg leeftijd in decimaal getal op basis van geboortedatum
Klik JAARDEEL voor details over de argumenten en het gebruik ervan.
Om bijvoorbeeld de leeftijden te krijgen op basis van de lijst met geboortedata in kolom B2:B9, gebruikt u de formule als volgt:
=YEARFRAC(B2,TODAY())
Media Enter en sleep vervolgens de hendel voor automatisch aanvullen omlaag totdat alle leeftijden zijn berekend.
Tip:
1) U kunt de decimale plaats specificeren zoals u nodig heeft in de Cellen opmaken dialoogvenster.
2) Als u de leeftijd op een specifieke datum wilt berekenen op basis van een bepaalde geboortedatum, wijzigt u VANDAAG() in de specifieke datum tussen dubbele aanhalingstekens, zoals =YEARFRAC(B2,"1/1/2021")
3) Als u de leeftijd van het volgende jaar wilt berekenen op basis van de geboortedatum, voegt u gewoon 1 toe aan de formule, zoals =YEARFRAC(B2,TODAY())+1.
Krijg leeftijd in geheel getal op basis van geboortedatum
Klik DATUM.IF voor details over de argumenten en het gebruik ervan.
Gebruik het bovenstaande voorbeeld om de leeftijd te krijgen op basis van de geboortedata in de lijst in B2:B9, gebruik de formule als volgt:
=DATEDIF(B2,TODAY(),"y")
Media Enter en sleep vervolgens de hendel voor automatisch aanvullen omlaag totdat alle leeftijden zijn berekend.
Tip:
1) Als u de leeftijd op een specifieke datum wilt berekenen op basis van een bepaalde geboortedatum, wijzigt u VANDAAG() in de specifieke datum tussen dubbele aanhalingstekens, zoals =DATEDIF(B2,,"1/1/2021","y") .
2) Als u de leeftijd van het volgende jaar wilt berekenen op basis van de geboortedatum, voegt u gewoon 1 toe aan de formule, zoals =DATEDIF(B2,TODAY(),"y")+1.
3.12 Bereken de leeftijd in jaren, maand en dagen op gegeven verjaardag
Als u de leeftijd wilt berekenen op basis van een bepaalde geboortedatum en het resultaat wilt weergeven als xx jaar, xx maanden, xx dagen zoals de onderstaande schermafbeelding laat zien, is hier een lange formule die u kan helpen.
Gebruik de formule als volgt om de leeftijd in jaren, maanden en dagen te krijgen op basis van de geboortedatum in cel B12:
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
Media Enter toets om de leeftijd te krijgen en sleep vervolgens de hendel voor automatisch aanvullen omlaag naar andere cellen.
Tip:
Als u de leeftijd op een specifieke datum wilt berekenen op basis van een bepaalde geboortedatum, wijzigt u VANDAAG() in de specifieke datum tussen dubbele aanhalingstekens zoals = =DATEDIF(B12,"1/1/2021","Y")& " Jaren, "&DATEDIF(B12,"1/1/2021","JM")&" Maanden, "&DATEDIF(B12,"1/1/2021","MD")&" Dagen".
3.13 Bereken leeftijd naar geboortedatum vóór 1/1/1900
In Excel kan de datum vóór 1/1/1900 niet als datetime worden ingevoerd of correct worden berekend. Maar als u de leeftijd van een beroemd persoon wilt berekenen op basis van de opgegeven geboortedatum (vóór 1/11900) en overlijdensdatum, kan alleen een VBA-code u helpen.
1. druk op anders + F11 toetsen om in te schakelen Microsoft Visual Basic voor toepassingen venster en klik Invoegen tab en kies Module om een nieuwe module te maken.
2. Kopieer en plak vervolgens de onderstaande code in de nieuwe module.
VBA: Bereken de leeftijd 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 en ga terug naar het blad en selecteer een cel om de berekende leeftijd te plaatsen, typ =AgeFunc(birthdate,deathdate), in dit geval, =LeeftijdFunc(B22,C22), druk op de Enter-toets om de leeftijd te krijgen. En gebruik de automatische vulgreep om deze formule indien nodig op andere cellen toe te passen.
Als je Kutools for Excel geïnstalleerd in Excel, kunt u de Helper voor datum en tijd hulpmiddel om de leeftijd te berekenen.
1. Selecteer een cel waarin u de berekende leeftijd wilt plaatsen en klik op Kutools > Formule Helper > Helper voor datum en tijd.
2. In de Helper voor datum en tijd dialoog,
- 1) Controleer Leeftijd keuze;
- 2) Kies de geboortedatumcel of voer direct de geboortedatum in of klik op het kalenderpictogram om de geboortedatum te selecteren;
- 3) Kies Heden optie als u de huidige leeftijd wilt berekenen, kiest u Opgegeven datum optie en voer de datum in als u de leeftijd in het verleden of in de toekomst wilt berekenen;
- 4) Geef het uitvoertype op in de vervolgkeuzelijst;
- 5) Bekijk een voorbeeld van het uitvoerresultaat. Klik Ok.
Klik Helper voor datum en tijd om meer te weten te komen over het gebruik van deze functie.
Klik Kutools for Excel om alle functies van deze invoegtoepassing te kennen.
Klik Gratis download om een gratis proefperiode van 30 dagen van Kutools voor Excel te krijgen
3.31 Verkrijg verjaardag van ID-nummer
Als er een lijst is met ID-nummers die de eerste 6 cijfers gebruiken om de geboortedatum vast te leggen, zoals 920315330 betekent dat de geboortedatum 03-15-1992 is, hoe kunt u dan snel de geboortedatum in een andere kolom krijgen?
Laten we nu de lijst met ID-nummers die in cel C2 beginnen als instantie nemen en de formule als volgt gebruiken:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Media Enter sleutel. Sleep vervolgens de hendel voor automatisch aanvullen naar beneden om andere resultaten te krijgen.
Opmerking:
In de formule kunt u de verwijzing naar uw behoefte wijzigen. Als het ID-nummer bijvoorbeeld wordt weergegeven als 13219920420392 en de geboortedatum 04-20-1992 is, kunt u de formule wijzigen in =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 met ID-nummers is die de eerste 6 cijfers gebruiken om de geboortedatum vast te leggen, zoals 920315330 betekent dat de geboortedatum 03-15-1992 is, hoe kunt u dan snel de leeftijd berekenen op basis van elk ID-nummer in Excel?
Laten we nu de lijst met ID-nummers die in cel C2 beginnen als instantie nemen en de formule als volgt 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")
Media Enter sleutel. Sleep vervolgens de hendel voor automatisch aanvullen naar beneden om andere resultaten te krijgen.
Opmerking:
In deze formule, als het jaar kleiner is dan het huidige jaar, wordt het jaar beschouwd als beginnend met 20, zoals 200203943 zal worden beschouwd als het jaar 2020; als het jaar groter is dan het huidige jaar, wordt het jaar beschouwd als beginnend met 19, zoals 920420392 wordt beschouwd als het jaar 1992.
Meer Excel-tutorials:
Combineer meerdere werkmappen/werkbladen in één
Deze zelfstudie bevat bijna alle combinaties van scenario's waarmee u te maken kunt krijgen en biedt relatieve professionele oplossingen voor u.
Tekst-, getal- en datumcellen splitsen (gescheiden in meerdere kolommen)
Deze tutorial is verdeeld in drie delen: gesplitste tekstcellen, gesplitste nummercellen en gesplitste datumcellen. Elk deel biedt verschillende voorbeelden om u te helpen weten hoe u de kloofklus moet aanpakken wanneer u hetzelfde probleem tegenkomt.
Combineer de inhoud van meerdere cellen zonder gegevens te verliezen in Excel
Deze zelfstudie beperkt de extractie tot een specifieke positie in een cel en verzamelt verschillende methoden om tekst of getallen uit een cel te extraheren op specifieke positie in Excel.
Vergelijk twee kolommen voor overeenkomsten en verschillen in Excel
Hier behandelt dit artikel de meeste mogelijke scenario's van het vergelijken van twee kolommen die u zou kunnen tegenkomen, en ik hoop dat het u kan helpen.
De beste tools voor kantoorproductiviteit
Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%
- Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
- Voeg cellen / rijen / kolommen samen en het bewaren van gegevens; Gespleten cellen inhoud; Combineer dubbele rijen en som / gemiddelde... Voorkom dubbele cellen; Vergelijk Ranges...
- Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
- Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
- Favoriete formules en snel invoegen, Bereiken, grafieken en afbeeldingen; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
- Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
- Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
- Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
- Draaitabel groeperen op weeknummer, dag van de week en meer ... Toon ontgrendelde, vergrendelde cellen door verschillende kleuren; Markeer cellen met formule / naam...
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- 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!
