Excel-handledning: Beräkning av datum och tid (beräkna skillnad, ålder, addera/subtrahera)
I Excel används beräkningen av datum och tid ofta, som att beräkna skillnaden mellan två datum/tider, lägga till eller subtrahera datum och tid, få ålder baserat på födelsedatumet och så vidare. Här i den här handledningen listar den nästan scenarier om beräkning av datum och tid och ger dig de relaterade metoderna.
I den här handledningen skapar jag några exempel för att förklara metoderna, du kan ändra referenserna för du behöver när du använder nedanstående VBA-kod eller formler
1. Beräkna skillnaden mellan två datum/tider
Att beräkna skillnaden mellan två datum eller två tider kan vara det mest normala problemet med datum- och tidsberäkning du möter i det dagliga Excel-arbetet. Följande exempel nedan kan hjälpa dig att förbättra effektiviteten när du stöter på samma problem.
1.11 Beräkna skillnaden mellan två datum i dagar/månader/år/veckor
Excel DATEDIF-funktionen kan användas för att snabbt beräkna skillnaden mellan två datum i dagar, månader, år och veckor.
Klicka för mer information om DATEDIF fungera
Dagsskillnad mellan två datum
För att få dagsskillnaden mellan två datum i cell A2 och B2, använd formeln som denna
=DATEDIF(A2,B2,"d")
Presse ange för att få resultatet.
Månaders skillnad mellan två datum
För att få månadsskillnaden mellan två datum i cell A5 och B5, använd formeln som denna
=DATEDIF(A5,B5,"m")
Presse ange för att få resultatet.
Årsskillnad mellan två datum
För att få årsskillnaden mellan två datum i cell A8 och B8, använd formeln som denna
=DATEDIF(A8,B8,"y")
Presse ange för att få resultatet.
Veckors skillnad mellan två datum
För att få veckors skillnad mellan två datum i cell A11 och B11, använd formeln som denna
=DATEDIF(A11,B11,"d")/7
Presse ange för att få resultatet.
Notera:
1) När du använder formeln ovan för att få veckors skillnad, kan det returnera ett resultat i datumformat, du måste formatera resultatet till allmänt eller antal som du behöver.
2) När du använder formeln ovan för att få veckorsskillnaden kan den återgå till ett decimaltal, om du vill få heltalsveckans nummer kan du lägga till funktionen RUNDA NEDÅT innan som nedan visas för att få heltalsveckorsskillnaden:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Beräkna månader ignorera år och dagar mellan två datum
Om du bara vill beräkna månadsskillnaden och ignorera år och dagar mellan två datum som skärmbilden nedan visar, här är en formel som kan hjälpa dig.
=DATEDIF(A2,B2,"ym")
Presse ange för att få resultatet.
A2 är startdatumet och B2 är slutdatumet.
1.13 Beräkna dagar ignorera år och månader mellan två datum
Om du bara vill beräkna dagsskillnaden utan att ignorera år och månader mellan två datum enligt skärmbilden nedan, här är en formel som kan hjälpa dig.
=DATEDIF(A5,B5,"md")
Presse ange för att få resultatet.
A5 är startdatumet och B5 är slutdatumet.
1.14 Beräkna skillnaden mellan två datum och returår, månader och dagar
Om du vill få skillnaden mellan två datum och returnera xx år, xx månader och xx dagar som skärmdumpen nedan visar, finns här också en formel.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
Presse ange för att få resultatet.
A8 är startdatumet och B8 är slutdatumet.
1.15 Beräkna skillnaden mellan ett datum och idag
För att automatiskt beräkna skillnaden mellan ett datum och idag, ändra bara slutdatumet i formlerna ovan till TODAY(). Ta här för att beräkna dagars skillnad mellan ett tidigare datum och idag som en instans.
=DATEDIF(A11,TODAY(),"d")
Presse ange för att få resultatet.
Anmärkningar: om du vill beräkna skillnaden mellan ett framtida datum och idag, ändra startdatum till idag och ta det framtida datumet som slutdatum så här:
=DATEDIF(TODAY(),A14,"d")
Observera att startdatum måste vara mindre än slutdatum i DATEDIF-funktionen, annars återgår det till #NUM! felvärde.
1.16 Beräkna arbetsdagar med eller utan semester mellan två datum
Ibland kan du behöva räkna antalet arbetsdagar med eller utan helgdagar mellan två givna datum.
I den här delen kommer du att använda funktionen NETWORKDAYS.INTL:
Klicka NETWORKDAYS.INTL att känna till dess argument och användning.
Räkna arbetsdagar med helgdagar
För att räkna arbetsdagarna med helgdagar mellan två datum i cell A2 och B2, använd formeln så här:
=NETWORKDAYS.INTL(A2,B2)
Presse ange för att få resultatet.
Räkna arbetsdagar utan helgdagar
För att räkna arbetsdagarna med helgdagar mellan två datum i cell A2 och B2, och exklusive helgdagar i intervallet D5:D9, använd formeln så här:
=NÄTVERKDAGAR.INTL(A5;B5,1;5;D9:DXNUMX)
Presse ange för att få resultatet.
Notera:
I formlerna ovan tar de lördag och söndag som helg, om du har olika helgdagar, ändra argumentet [helg] som du behöver.
1.17 Beräkna helger mellan två datum
Om du vill räkna antalet helger mellan två datum kan SUMPRODUKT- eller SUMMA-funktionerna göra dig en tjänst.
Så här räknar du helgerna (lördag och söndag) mellan två datum i cell A12 och B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Presse ange för att få resultatet.
1.18 Beräkna specifik veckodag mellan två datum
För att räkna numret på en specifik veckodag som måndag mellan två datum kan kombinationen av funktionerna INT och WEEKDAY hjälpa dig.
Cell A15 och B15 är de två datum som du vill räkna måndag mellan, använd formeln så här:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Presse ange för att få resultatet.
Ändra veckodagens nummer i WEEKDAY-funktionen för att räkna en annan veckodag:
1 är söndag, 2 är måndag, 3 är tisdag, 4 är onsdag, 5 är torsdag, 6 är fredag och 7 är lördag)
1.19 Beräkna återstående dagar i månad/år
Ibland kanske du vill veta de återstående dagarna i månaden eller året baserat på det angivna datumet som skärmbilden nedan visar:
Få återstående dagar i innevarande månad
Klicka EOMONTH att känna till argumentet och användningen.
För att få de återstående dagarna av den aktuella månaden i cell A2, använd formeln så här:
=EOMONTH(A2,0)-A2
Presse ange och dra autofyllhandtaget för att tillämpa denna formel på andra celler om det behövs.
Dricks: resultaten kan visas som datumformat, ändra dem bara som allmänt eller sifferformat.
Få återstående dagar under innevarande år
För att få de återstående dagarna av innevarande år i cell A2, använd formeln så här:
=DATE(YEAR(A2),12,31)-A2
Presse ange och dra autofyllhandtaget för att tillämpa denna formel på andra celler om det behövs.
1.21 Beräkna skillnaden mellan två tider
För att se skillnaden mellan två tider, här är två enkla formler som kan hjälpa dig.
Antag att i cell A2 och B2 innehåller start_time och end_time separat, med formlerna som dessa:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Presse ange för att få resultatet.
Notera:
- Om du använder end_time-start_time kan du formatera resultatet till ett annat tidsformat som du behöver i dialogrutan Formatera celler.
- Om du använder TEXT(end_time-first_time,"time_format"), ange det tidsformat du vill att resultatet ska visas i formeln, till exempel TEXT(end_time-first_time,"h") returnerar 16.
- Om sluttiden är mindre än starttid returnerar båda formlerna felvärden. För att lösa det här problemet kan du lägga till ABS framför dessa formler, såsom ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), och formatera sedan resultatet som tid.
1.22 Beräkna skillnaden mellan två tider i timmar/minuter/sekunder
Om du vill beräkna skillnaden mellan två gånger i timmar, minuter eller sekunder som skärmdumpen nedan visar, vänligen följ den här delen.
Få timmars skillnad mellan två tider
För att få timskillnaden mellan två tider i A5 och B5, använd formeln så här:
=INT((B5-A5)*24)
Presse ange och formatera sedan tidsformatresultatet som allmänt eller nummer.
Om du vill få decimaltimmarsskillnaden, använd (sluttid-starttid)*24.
Få minuters skillnad mellan två tider
För att få minutskillnaden mellan två gånger i A8 och B8, använd formeln så här:
=INT((B8-A8)*1440)
Presse ange och formatera sedan tidsformatresultatet som allmänt eller nummer.
Om du vill få decimalminutersskillnaden, använd (end_time-start_time)*1440.
Få sekunders skillnad mellan två tider
För att få sekundersskillnaden mellan två gånger i A5 och B5, använd formeln så här:
=(B11-A11)*86400)
Presse ange och formatera sedan tidsformatresultatet som allmänt eller nummer.
1.23 Beräkna timmars skillnad endast mellan två gånger (inte överstiga 24 timmar)
Om skillnaden mellan två tider inte överstiger 24 timmar kan HOUR-funktionen snabbt få fram timskillnaden mellan dessa två tider.
Klicka TIMME för mer information om denna funktion.
För att få timskillnaden mellan tiderna i cell A14 och B14, använd HOUR-funktionen så här:
=HOUR(B14-A14)
Presse ange för att få resultatet.
Starttid måste vara mindre än sluttid, annars returnerar formeln #NUM! felvärde.
1.24 Beräkna minuters skillnad endast mellan två gånger (inte överstiga 60 minuter)
MINUTE-funktionen kan snabbt få den enda minutskillnaden mellan dessa två tider och ignorera timmar och sekunder.
Klicka MINUT för mer information om denna funktion.
För att bara få minutskillnaden mellan tiderna i cell A17 och B17, använd MINUTE-funktionen så här:
=MINUTE(B17-A17)
Presse ange för att få resultatet.
Starttid måste vara mindre än sluttid, annars returnerar formeln #NUM! felvärde.
1.25 Beräkna sekunders skillnad endast mellan två gånger (inte överstiga 60 sekunder)
Funktionen ANDRA kan snabbt få den enda sekundskillnaden mellan dessa två tider och ignorera timmar och minuter.
Klicka ANDRA för mer information om denna funktion.
För att bara få sekundersskillnaden mellan tiderna i cell A20 och B20, använd den ANDRA funktionen så här:
=SECOND(B20-A20)
Presse ange för att få resultatet.
Starttid måste vara mindre än sluttid, annars returnerar formeln #NUM! felvärde.
1.26 Beräkna skillnaden mellan två tider och returtimmar, minuter, sekunder
Om du vill visa skillnaden mellan två gånger som xx timmar xx minuter xx sekunder, använd TEXT-funktionen enligt nedan:
Klicka TEXT för att inse argumenten och användningen av denna funktion.
För att beräkna skillnaden mellan tider i cell A23 och B23, använd formeln så här:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Presse ange för att få resultatet.
Notera:
Den här formeln beräknar också bara timmarsskillnaden som inte överstiger 24 timmar, och sluttiden måste vara större än starttid, annars returnerar den #VÄRDE! felvärde.
1.27 Beräkna skillnaden mellan två datumtider
Om det finns två tider i formatet mm/dd/åååå hh:mm:ss, för att beräkna skillnaden mellan dem, kan du använda en av nedanstående formler efter behov.
Få tidsskillnaden mellan två datumtider och returnera resultatet i formatet hh:mm:ss
Ta två datumtider i cell A2 och B2 som exempel, använd formeln så här:
=B2-A2
Presse ange nyckel, returnerar ett resultat i datetime-format, formatera sedan detta resultat som [H]: mm: ss i den anpassade kategorin under Antal flik i Formatera celler dialog.
Få skillnaden mellan två datumtider och returdagar, timmar, minuter, sekunder
Ta två datumtider i cell A5 och B5 som exempel, använd formeln så här:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Presse ange för att få resultatet.
Obs! I båda formlerna måste slutdatumtid vara större än startdatumtid, annars returnerar formlerna felvärden.
1.28 Beräkna tidsskillnaden med millisekunder
Först måste du veta hur du formaterar cellen för att visa millisekunderna:
Markera cellerna som du vill visa millisekunder och höger för att välja Formatera celler för att aktivera Formatera celler dialogrutan, välj Custom i Kategori lista under Nummerfliken och skriv detta hh: mm: ss.000 in i textrutan.
Använd formel:
Här för att beräkna skillnaden mellan två gånger i cell A8 och B8, använd formeln som:
=ABS(B8-A8)
Presse ange för att få resultatet.
1.29 Beräkna arbetstid mellan två datum exklusive helger
Ibland kan du behöva räkna arbetstiden mellan två datum, exklusive helger (lördag och söndag).
Här är arbetstiden fastställd till 8 timmar varje dag, och för att beräkna arbetstiden mellan två datum som anges i cell A16 och B16, använd formeln så här:
=NETWORKDAYS(A16,B16) * 8
Presse ange och formatera sedan resultatet som allmänt eller nummer.
För fler exempel på att beräkna arbetstid mellan två datum, besök Få arbetstid mellan två datum i Excel
Om du har Kutools för Excel installerat i Excel kan 90 procent av beräkningar av datum- och tidsskillnad snabbt lösas utan att komma ihåg några formler.
1.31 Beräkna skillnaden mellan två datumtider av Data & Time Helper
För att beräkna skillnaden mellan två datumtider i Excel, bara Hjälp för datum och tid är nog.
1. Välj en cell där du placerar det beräknade resultatet och klicka Kutools > Formel Helper > Hjälp för datum och tid.
2. I popping Hjälp för datum och tid dialogrutan, följ inställningarna nedan:
- Kolla upp Skillnaden alternativ;
- Välj startdatumtid och slutdatumtid i Argumentinmatning kan du också direkt skriva in datum och tid manuellt i inmatningsrutan, eller klicka på kalenderikonen för att välja datum;
- Välj utdataresultattyp från rullgardinsmenyn;
- Förhandsgranska resultatet i Resultat sektion.
3. klick Ok. Det beräknade resultatet matas ut och dra autofyllhandtaget över cellerna som du också behöver beräkna.
Dricks:
Om du vill få skillnaden mellan två datum och tid och visa resultatet som dagar, timmar och minuter med Kutools för Excel, vänligen gör som nedan:
Välj en cell där du vill placera resultatet och klicka Kutools > Formel Helper > Datum och tid > Räkna dagar, timmar och minuter mellan två datum.
Sedan i Formlerhjälpare dialogrutan, ange startdatum och slutdatum och klicka sedan Ok.
Och skillnadsresultatet kommer att visas som dagar, timmar och minuter.
Klicka Hjälp för datum och tid för att veta mer om användningen av den här funktionen.
Klicka Kutools för Excel att känna till alla funktioner i detta tillägg.
Klicka Gratis nedladdning för att få 30 dagars gratis provversion av Kutools för Excel
1.32 Beräkna helg/arbetsdag/specifik veckodag skillnad mellan två datumtider av Formula Helper
Om du snabbt vill räkna helgen, arbetsdagarna eller en specifik veckodag mellan två datumtider, Kutools for Excel's Formel Helper gruppen kan hjälpa dig.
1. Välj cellen som ska placera det beräknade resultatet, klicka Kutools > Statistisk > Antal icke-arbetsdagar mellan två datum/Antal arbetsdagar mellan två datum/Räkna antalet specifika veckodagar.
2. I popping ut Formlerhjälpare dialogrutan, ange startdatum och slutdatum, om du ansöker Räkna antalet specifika veckodagar, måste du också ange veckodag.
För att räkna den specifika veckodagen kan du hänvisa till lappen för att använda 1-7 för att indikera söndag-lördag.
3. klick Ok, och dra sedan autofyllhandtaget över celler som behöver räkna antalet helger/arbetsdagar/specifika veckodagar om det behövs.
Klicka Kutools för Excel att känna till alla funktioner i detta tillägg.
Klicka Gratis nedladdning för att få 30 dagars gratis provversion av Kutools för Excel
2. Lägg till eller subtrahera datum och tid
Förutom att beräkna skillnaden mellan två datetimes, addera eller subtrahera är också den normala datetime-beräkningen i Excel. Till exempel kanske du vill få förfallodatum baserat på produktionsdatum och antalet bevarandedagar för en produkt.
2.11 Lägg till eller subtrahera dagar till ett datum
För att lägga till eller subtrahera ett visst antal dagar till ett datum, här är två olika metoder.
Om du antar att du lägger till 21 dagar till ett datum i cell A2, välj en av metoderna nedan för att lösa det,
Metod 1 datum+dagar
Välj en cell och skriv formeln:
=A+21
Presse ange för att få resultatet.
Om du vill subtrahera 21 dagar, byter juts plustecken (+) till minustecken (-).
Metod 2 Klistra in special
1. Skriv in antalet dagar du vill lägga till i en cell i cell C2 och tryck sedan på ctrl + C att kopiera den.
2. Välj sedan de datum du vill lägga till 21 dagar, högerklicka för att visa snabbmenyn och välj Klistra in special....
3. I Klistra in special dialogrutan, kontrollera Lägg till (Om du vill subtrahera dagar, markera Subtrahera alternativ). Klick OK.
4. Nu ändras de ursprungliga datumen till 5-siffriga nummer, formatera dem som datum.
2.12 Lägg till eller subtrahera månader till ett datum
För att lägga till eller subtrahera månader till ett datum kan EDATE-funktionen användas.
Klicka EDATE att studera dess argument och användning.
Anta att du lägger till 6 månader till datumet i cell A2, använd formeln så här:
=EDATE(A2,6)
Presse ange för att få resultatet.
Om du vill subtrahera 6 månader till datumet, ändra 6 till -6.
2.13 Lägg till eller subtrahera år till ett datum
För att lägga till eller subtrahera n år till ett datum kan en formel som kombinerar funktionerna DATUM, ÅR, MÅNAD och DAG användas.
Anta att du lägger till 3 år till datumet i cell A2, använd formeln så här:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Presse ange för att få resultatet.
Om du vill dra av 3 år till datumet, ändra 3 till -3.
2.14 Lägg till eller subtrahera veckor till ett datum
För att lägga till eller subtrahera veckor till ett datum är den allmänna formeln
Anta att du lägger till 4 veckor till datumet i cell A2, använd formeln så här:
=A2+4*7
Presse ange för att få resultatet.
Om du vill subtrahera 4 veckor till datumet, ändra plustecken (+) till minustecken (-).
2.15 Lägg till eller dra av arbetsdagar inklusive eller exklusive helgdagar
I det här avsnittet introduceras hur man använder ARBETSDAG-funktionen för att lägga till eller subtrahera arbetsdagar till ett givet datum exklusive helgdagar eller inklusive helgdagar.
Besök ARBETSDAG för att veta mer detaljer om dess argument och användning.
Lägg till arbetsdagar inklusive helgdagar
I cell A2 är datumet som du använder, i cell B2 innehåller antalet dagar som du vill lägga till, använd formeln så här:
=WORKDAY(A2,B2)
Presse ange för att få resultatet.
Lägg till arbetsdagar exklusive helgdagar
I cell A5 är datumet som du använder, i cell B5 innehåller antalet dagar som du vill lägga till, i intervallet D5:D8 listar helgdagarna, använd formeln så här:
=WORKDAY(A5,B5,D5:D8)
Presse ange för att få resultatet.
Notera:
WORKDAY-funktionen tar lördag och söndag som helger, om dina helger är på lördagar och söndagar kan du använda WOKRDAY.INTL-funktionen, som stöder att ange helger.
Besök ARBETSDAG.INTL för mer detaljer.
Om du vill subtrahera arbetsdagar till ett datum, ändra bara antalet dagar till negativt i formeln.
2.16 Lägg till eller subtrahera specifikt år, månad, dagar till ett datum
Om du vill lägga till ett specifikt år, månadsdagar till ett datum, kan formeln som kombinerar funktionen DATUM, ÅR, MÅNAD och DAGAR göra dig en tjänst.
För att lägga till 1 år 2 månader och 30 dagar till ett datum i A11, använd formeln så här:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Presse ange för att få resultatet.
Om du vill subtrahera, ändra alla plustecken (+) till minustecken (-).
2.21 Lägg till eller subtrahera timmar/minuter/sekunder till en datumtid
Här finns några formler för att lägga till eller subtrahera timmar, minuter eller sekunder till en datumtid.
Lägg till eller subtrahera timmar till en datumtid
Om du antar att du lägger till 3 timmar till en datumtid (kan också vara en tid) i cell A2, använd formeln så här:
=A2+3/24
Presse ange för att få resultatet.
Lägg till eller subtrahera timmar till en datumtid
Om du antar att du lägger till 15 minuter till en datumtid (kan också vara en tid) i cell A5, använd formeln så här:
=A2+15/1440
Presse ange för att få resultatet.
Lägg till eller subtrahera timmar till en datumtid
Om du antar att du lägger till 20 sekunder till en datetime (kan också vara en tid) i cell A8, använd formeln så här:
=A2+20/86400
Presse ange för att få resultatet.
2.22 Summa tider över 24 timmar
Om det finns en Excel-tabell som registrerar arbetstiden för alla anställda under en vecka, för att summera den totala arbetstiden för att beräkna betalningarna, kan du använda SUM(intervall) för att få resultatet. Men generellt sett kommer det summerade resultatet att visas som en tid som inte överstiger 24 timmar som skärmdumpen nedan visar, hur kan du få rätt resultat?
Egentligen behöver du bara formatera resultatet som [hh]:mm:ss.
Högerklicka på resultatcellen, välj Formatera celler i snabbmenyn och i popparet Formatera celler dialog, välj Custom från blisten, och skriv [hh]:mm:ss i textrutan i höger avsnitt, klicka OK.
Det summerade resultatet kommer att visas korrekt.
2.23 Lägg till arbetstider till ett datum exklusive helg och helgdag
Här tillhandahålls en lång formel för att få slutdatumet baserat på att lägga till ett specifikt antal arbetstimmar till ett startdatum och exkluderar helger (lördag och söndag) och helgdagar.
I en Excel-tabell innehåller A11 startdatumtid och B11 innehåller arbetstider, i cell E11 och E13 är arbetsstart- och sluttiderna och cell E15 innehåller helgdagen som kommer att exkluderas.
Använd formeln så här:
=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))
Presse ange för att få resultatet.
Om du har installerat Kutools för Excel, bara ett verktyg – Hjälp med datum och tidr kan lösa de flesta beräkningarna med att addera och subtrahera datumtid.
1. Klicka på en cell som du vill visa resultatet och använd det här verktyget genom att klicka Kutools > Formel Helper > Hjälp för datum och tid.
2. I Hjälp för datum och tid dialogrutan, kontrollera Lägg till alternativ eller Subtrahera alternativet som du behöver, välj sedan cellen eller skriv direkt in den datumtid du vill använda i Argumentinmatning avsnitt, ange sedan år, månader, veckor, dagar, timmar, minuter och sekunder som du vill lägga till eller subtrahera, klicka sedan på Ok. Se skärmdump:
Du kan förhandsgranska det beräknade resultatet i Resultat sektion.
Nu är resultatet matat ut, dra autohandtaget över andra celler för att få resultatet.
Klicka Hjälp för datum och tid för att veta mer om användningen av den här funktionen.
Klicka Kutools för Excel att känna till alla funktioner i detta tillägg.
Klicka Avgiftsnedladdning för att få 30 dagars gratis provversion av Kutools för Excel
2.41 Markera eller markera om ett datum har gått ut
Om det finns en lista över utgångsdatum för produkter, kanske du vill kontrollera och markera de datum som är utgångna baserat på idag som skärmbilden nedan visar.
Faktum är att Villkorlig formatering kan snabbt hantera detta jobb.
1. Välj de datum som du vill kontrollera och klicka sedan Hem > Villkorlig formatering > Ny regel.
2. I Ny formateringsregel dialogrutan, välj Använd en formel för att bestämma vilka celler som ska formateras i Välj en regeltyp avsnitt och typ =B2 i inmatningsrutan (B2 är det första datumet du vill kontrollera) och klicka bildad att dyka upp Formatera celler dialogrutan och välj sedan en annan formatering för att överstiga utgångsdatumen som du behöver. Klick OK > OK.
2.42 Returnera slutet av innevarande månad/första dagen i nästa månad/a>
Sista utgångsdatum för vissa produkter är i slutet av produktionsmånaden eller den första dagen i nästa produktionsmånad, för att snabbt lista utgångsdatumen baserat på produktionsdatumet, följ denna del.
Få slutet av innevarande månad
Här är ett produktionsdatum i cell B13, använd formeln så här:
=EOMONTH(B13,0)
Presse ange för att få resultatet.
Få 1:a dagen i nästa månad
Här är ett produktionsdatum i cell B18, använd formeln så här:
=EOMONTH(B18,0)+1
Presse ange för att få resultatet.
3. Beräkna ålder
I det här avsnittet listar det metoderna för att lösa hur man beräknar ålder baserat på ett givet datum eller ett serienummer.
3.11 Beräkna ålder baserat på givet födelsedatum
Få ålder i decimaltal baserat på födelsedatum
Klicka ÅRFRAC för detaljer om dess argument och användning.
Till exempel, för att få åldrarna baserat på listan över födelsedatum i kolumn B2:B9, använd formeln så här:
=YEARFRAC(B2,TODAY())
Presse ange och dra sedan autofyllhandtaget nedåt tills alla åldrar har beräknats.
Dricks:
1) Du kan ange decimalen som du behöver i Formatera celler dialog.
2) Om du vill beräkna åldern på ett specifikt datum baserat på ett givet födelsedatum, ändra TODAY() till det specifika datumet med dubbla citattecken såsom =YEARFRAC(B2,"1/1/2021")
3) Om du vill få nästa års ålder baserat på födelsedatumet, lägg bara till 1 i formeln såsom =ÅRFRAC(B2,IDAG())+1.
Få ålder i heltal baserat på födelsedatum
Klicka DATEDIF för detaljer om dess argument och användning.
Med hjälp av exemplet ovan, för att få åldern baserad på födelsedatumen i listan i B2:B9, använd formeln så här:
=DATEDIF(B2,TODAY(),"y")
Presse ange och dra sedan handtaget för automatisk fyllning nedåt tills alla åldrar har beräknats.
Dricks:
1) Om du vill beräkna åldern på ett specifikt datum baserat på ett givet födelsedatum, ändra TODAY() till det specifika datumet med dubbla citattecken såsom =DATEDIF(B2,"1/1/2021","y") .
2) Om du vill få nästa års ålder baserat på födelsedatumet, lägg bara till 1 i formeln såsom =DATEDIF(B2,IDAG(),"y")+1.
3.12 Beräkna ålder i format för år, månad och dagar efter given födelsedag
Om du vill beräkna ålder baserat på ett givet födelsedatum, och visa resultatet som xx år, xx månader, xx dagar som skärmdumpen nedan visar, är här en lång formel som kan hjälpa dig.
För att få åldern i år, månader och dagar baserat på födelsedatumet i cell B12, använd formeln så här:
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
Presse ange för att få åldern, dra sedan autofyllhandtaget ner till andra celler.
Dricks:
Om du vill beräkna åldern i ett specifikt datum baserat på ett givet födelsedatum, ändra TODAY() till det specifika datumet med dubbla citattecken som = =DATEDIF(B12,"1/1/2021","Y")& " År, "&DATEDIF(B12,"1/1/2021","YM")&" Månader, "&DATEDIF(B12,"1/1/2021","MD")&" Dagar".
3.13 Beräkna ålder efter födelsedatum före 1/1/1900
I Excel kan datumet före 1/1/1900 inte anges som datumtid eller beräknas korrekt. Men om du vill beräkna åldern på en känd person baserat på det givna födelsedatumet (före 1/11900) och dödsdatumet, kan bara en VBA-kod hjälpa dig.
1. Tryck andra + F11 för att aktivera Microsoft Visual Basic för applikationer och klicka Insert fliken och välj Modulerna för att skapa en ny modul.
2. Kopiera sedan och klistra in koden nedan i den nya modulen.
VBA: Beräkna ålder före 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. Spara koden och gå tillbaka till arket och välj en cell för att placera den beräknade åldern, skriv =AgeFunc(födelsedatum,dödsdatum), i det här fallet, =AgeFunc(B22;C22), tryck på Enter för att få åldern. Och använd autofyllhandtaget för att tillämpa den här formeln på andra celler om det behövs.
Om du har Kutools för Excel installerat i Excel kan du använda Hjälp för datum och tid verktyg för att beräkna åldern.
1. Välj en cell som du vill placera den beräknade åldern på och klicka på Kutools > Formel Helper > Hjälp för datum och tid.
2. I Hjälp för datum och tid dialog,
- 1) Kontrollera Ålder alternativ;
- 2) Välj födelsedatumcell eller ange födelsedatumet direkt eller klicka på kalenderikonen för att välja födelsedatum;
- 3) Välj I dag alternativet om du vill beräkna aktuell ålder, välj Specificerat datum alternativet och ange datumet om du vill beräkna åldern i det förflutna eller framtiden;
- 4) Ange utgångstypen från rullgardinsmenyn;
- 5) Förhandsgranska resultatet. Klick Ok.
Klicka Hjälp för datum och tid för att veta mer om användningen av den här funktionen.
Klicka Kutools för Excel att känna till alla funktioner i detta tillägg.
Klicka Gratis nedladdning för att få 30 dagars gratis provversion av Kutools för Excel
3.31 Få födelsedag från ID-nummer
Om det finns en lista med ID-nummer som använder de första 6 siffrorna för att registrera födelsedatumet, till exempel 920315330 betyder att födelsedatumet är 03-15-1992, hur kan du snabbt få in födelsedatumet i en annan kolumn?
Låt oss nu ta listan över ID-nummer som börjar i cell C2 som en instans och använda formeln så här:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Presse ange nyckel. Dra sedan autofyllhandtaget nedåt för att få andra resultat.
Notera:
I formeln kan du ändra referensen till ditt behov. Till exempel, om ID-numret visas som 13219920420392, födelsedagen är 04-20-1992, kan du ändra formeln till =MID(C2,8,2)&"/"&MID(C2,10,2)&"/ "&MID(C2,4,4) för att få rätt resultat.
3.32 Beräkna ålder från ID-nummer
Om det finns en lista med ID-nummer som använder de första 6 siffrorna för att registrera födelsedatumet, till exempel 920315330 betyder att födelsedatumet är 03-15-1992, hur kan du snabbt beräkna åldern baserat på varje ID-nummer i Excel?
Låt oss nu ta listan över ID-nummer som börjar i cell C2 som en instans och använda formeln så här:
=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")
Presse ange nyckel. Dra sedan autofyllhandtaget nedåt för att få andra resultat.
Notera:
I den här formeln, om året är mindre än det aktuella året, kommer året att anses börja med 20, till exempel 200203943 kommer att betraktas som år 2020; om året är större än innevarande år, kommer året att anses börja med 19, till exempel 920420392 kommer att betraktas som år 1992.
Fler Excel-tutorials:
Kombinera flera arbetsböcker/arbetsblad till ett
Denna handledning listar nästan alla kombinationsscenarier du kan möta och ger dig relativt professionella lösningar.
Dela text-, nummer- och datumceller (dela upp i flera kolumner)
Denna handledning är uppdelad i tre delar: delade textceller, delade nummerceller och delade datumceller. Varje del ger olika exempel som hjälper dig att veta hur du ska hantera klyvningsjobbet när du stöter på samma problem.
Kombinera innehållet i flera celler utan att förlora data i Excel
Denna handledning begränsar extraheringen till en specifik position i en cell och samlar olika metoder för att hjälpa till att extrahera text eller siffror från en cell efter specifik position i Excel.
Jämför två kolumner för matchningar och skillnader i Excel
Här täcker den här artikeln de flesta möjliga scenarier av de två jämförande kolumnerna du kan möta, och hoppas att det kan hjälpa dig.
De bästa Office-produktivitetsverktygen
Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%
- Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
- Sammanfoga celler / rader / kolumner och förvaring av data; Delat cellinnehåll; Kombinera duplicerade rader och summa / genomsnitt... Förhindra duplicerade celler; Jämför intervall...
- Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
- Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
- Favorit och sätt snabbt in formler, Intervall, diagram och bilder; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
- Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
- Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
- Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
- Gruppering av pivottabell efter veckonummer, veckodagen och mer ... Visa olåsta, låsta celler av olika färger; Markera celler som har formel / namn...
- Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
- Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
- Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!
