Excel oktatóanyag: Dátum és idő számítása (különbség kiszámítása, életkor, összeadás/kivonás)

Az Excelben gyakran használják a dátum- és időszámítást, például a két dátum/idő közötti különbség kiszámítását, a dátum-idő összeadását vagy kivonását, a születési dátum alapján az életkor meghatározását és így tovább. Ebben az oktatóanyagban szinte forgatókönyveket sorol fel a dátum- és időszámítással kapcsolatban, és megadja a kapcsolódó módszereket.

Az oktatóanyag navigációi

1. Számítsa ki két dátum/idő közötti különbséget 1.1 Számítsa ki a két dátum közötti nap/hónap/év különbséget 1.11 Számítsa ki a két dátum közötti különbséget napokban/hónapokban/években/hetekben 1.12 A hónapok számítása figyelmen kívül hagyja az éveket és a két dátum közötti napokat 1.13 A napok számítása figyelmen kívül hagyja az éveket és a hónapokat két dátum között 1.14 Számítsa ki a különbséget két dátum és a visszaküldési évek, hónapok és napok között 1.15 Számítsa ki a dátum és a mai nap közötti különbséget 1.16 Számítsa ki a munkanapokat ünnepnappal vagy anélkül két dátum között 1.17 Számítsa ki a hétvégéket két dátum között 1.18 Konkrét hétköznap kiszámítása két dátum között 1.19 Számolja ki a hátralévő napokat hónapban/évben

1.2 Számítsa ki a különbséget két idő között 1.21 Számítsa ki a két idő közötti különbséget 1.22 Számítsa ki a különbséget két idő között óra/perc/másodperc értékben 1.23 Csak két idő közötti órakülönbséget számítson ki (nem haladhatja meg a 24 órát) 1.24 Csak két idő között számítsa ki a perc különbséget (nem haladhatja meg a 60 percet) 1.25 Csak két idő között számítsa ki a másodperces különbséget (nem haladhatja meg a 60 másodpercet) 1.26 Számítsa ki a különbséget a két idő és a visszatérési órák, percek, másodpercek között 1.27 Két dátum és időpont közötti különbség kiszámítása 1.28 Számítsa ki az időkülönbséget ezredmásodpercekkel 1.29 Számítsa ki a munkaidőt két dátum között, a hétvégék kivételével

1.3 Számítsa ki két dátum és idő közötti különbséget a Kutools for Excel segítségével 1.31 A Data & Time Helper segítségével kiszámíthatja a két dátum és időpont közötti különbséget 1.32 Hétvége/munkanap/konkrét hétköznapi különbség kiszámítása két dátum között a Formula Helper segítségével

1.4 Ha üres, kombináljon két oszlopot 1.41 IF funkció használata 1.42 VBA használata

2. Adja hozzá vagy vonja ki a dátumot és az időt 2.1 Napok/hónapok/évek/hetek/munkanapok hozzáadása vagy kivonása egy dátumhoz 2.11 Napok hozzáadása vagy kivonása egy dátumhoz 2.12 Hónapok hozzáadása vagy kivonása egy dátumhoz 2.13 Évek hozzáadása vagy kivonása egy dátumhoz 2.14 Hetek hozzáadása vagy kivonása egy dátumhoz 2.15 Munkanapok összeadása vagy kivonása az ünnepnapokkal együtt vagy azok nélkül 2.16 Adott év, hónap, napok hozzáadása vagy kivonása egy dátumhoz

2.2 Órák/percek/másodpercek hozzáadása vagy kivonása az időhöz 2.21 Órák/percek/másodpercek hozzáadása vagy kivonása a dátumidőhöz 2.22 Összesített idők 24 órán belül 2.23 Munkaidő hozzáadása egy dátumhoz a hétvége és az ünnepnapok kivételével

2.3 Dátum/idő hozzáadása vagy kivonása a Kutools for Excel segítségével

2.4 Hosszabbítás 2.41 Ellenőrizze vagy jelölje ki, ha egy dátum lejárt 2.42 Az aktuális hónap végét/a következő hónap első napját adja vissza

3. Számítsa ki az életkort 3.1 Számítsa ki az életkort dátum alapján 3.11 Számítsa ki az életkort az adott születési dátum alapján 3.12 Számítsa ki az életkort években, hónapban és napokban, adott születésnap alapján 3.13 Az életkor kiszámítása 1. 1. 1900. előtti születési dátum alapján

3.2 Számítsa ki az életkort születés szerint a Kutools for Excel segítségével

3.3 Számolja ki az életkort vagy szerezze be a születési dátumot egy sorozatszám alapján 3.31 Születésnap megszerzése az azonosító számból 3.32 Számítsa ki az életkort az azonosítószámból

 

Ebben az oktatóanyagban néhány példát hozok létre a metódusok magyarázatára, és módosíthatja a szükséges hivatkozásokat, ha az alábbi VBA kódot vagy képleteket használja

---

1. Számítsa ki két dátum/idő közötti különbséget

A két dátum vagy két időpont közötti különbség kiszámítása lehet a legszokványosabb probléma a dátum- és időszámítás során, amellyel a napi Excel-munka során találkozik. Az alábbi példák segítségével növelheti a hatékonyságot, ha ugyanazokkal a problémákkal találkozik.

1.11 Számítsa ki a két dátum közötti különbséget napokban/hónapokban/években/hetekben

Az Excel DATEDIF funkciója segítségével gyorsan kiszámítható a különbség két dátum között napokban, hónapokban, években és hetekben.

Kattintson a további részletekért DATEDIF funkció

Napok eltérése két dátum között

Az A2 és B2 cellában lévő két dátum közötti napok különbségének kiszámításához használja a következő képletet

=DATEDIF(A2,B2,"d")

nyomja meg belép kulcs az eredmény eléréséhez.

Hónapos különbség két dátum között

Az A5 és B5 cellában lévő két dátum közötti hónapok közötti különbség kiszámításához használja a következő képletet

=DATEDIF(A5,B5,"m")

nyomja meg belép kulcs az eredmény eléréséhez.

Év különbség van két dátum között

Az A8 és B8 cellában lévő két dátum közötti évek közötti különbség kiszámításához használja a következő képletet

=DATEDIF(A8,B8,"y")

nyomja meg belép kulcs az eredmény eléréséhez.

Hetes különbség van két dátum között

Az A11 és B11 cellában lévő két dátum közötti hetes különbség kiszámításához használja a következő képletet

=DATEDIF(A11,B11,"d")/7

nyomja meg belép kulcs az eredmény eléréséhez.

Jegyzet:

1) Ha a fenti képletet használja a hetek különbségének kiszámításához, akkor előfordulhat, hogy dátum formátumban ad vissza eredményt. Az eredményt általános vagy számszerű formátumba kell formáznia, ahogyan szüksége van rá.

2) Ha a fenti képletet használja a hetek különbségének kiszámításához, akkor az visszatérhet egy tizedes számra, ha a hét egész számát szeretné megkapni, hozzáadhat egy ROUNDDOWN függvényt az alábbiak szerint, hogy megkapja az egész hetek különbségét:

=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)

1.12 A hónapok számítása figyelmen kívül hagyja az éveket és a két dátum közötti napokat

Ha csak a hónapok közötti különbséget szeretné kiszámítani, figyelmen kívül hagyva az éveket és a napokat két dátum között, ahogy az alábbi képernyőképen látható, itt van egy képlet, amely segíthet.

=DATEDIF(A2,B2,"ym")

nyomja meg belép kulcs az eredmény eléréséhez.

Az A2 a kezdő dátum, a B2 pedig a befejezés dátuma.

1.13 A napok számítása figyelmen kívül hagyja az éveket és a hónapokat két dátum között

Ha csak a napok különbségét szeretné kiszámítani, figyelmen kívül hagyva az éveket és a hónapokat két dátum között, amint az alábbi képernyőképen látható, itt van egy képlet, amely segíthet.

=DATEDIF(A5,B5,"md")

nyomja meg belép kulcs az eredmény eléréséhez.

Az A5 a kezdő dátum, a B5 pedig a befejezés dátuma.

1.14 Számítsa ki a különbséget két dátum és a visszaküldési évek, hónapok és napok között

Ha szeretné megkapni a különbséget két dátum között, és xx évet, xx hónapot és xx napot szeretne visszaküldeni, ahogy az alábbi képernyőképen is látható, itt van egy képlet is.

=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"

nyomja meg belép kulcs az eredmény eléréséhez.

Az A8 a kezdő dátum, a B8 pedig a befejezés dátuma.

1.15 Számítsa ki a dátum és a mai nap közötti különbséget

A dátum és a mai nap közötti különbség automatikus kiszámításához módosítsa a fenti képletekben az end_date értéket TODAY() értékre. Itt lehet kiszámítani a napok közötti különbséget egy múltbeli dátum és a mai nap között.

=DATEDIF(A11,TODAY(),"d")

nyomja meg belép kulcs az eredmény eléréséhez.

Megjegyzések: ha ki szeretné számítani a különbséget egy jövőbeli dátum és a mai nap között, módosítsa a start_date értéket a mai napra, és a jövőbeli dátumot használja end_dateként a következőképpen:

=DATEDIF(TODAY(),A14,"d")

Figyelje meg, hogy a start_date értéknek kisebbnek kell lennie, mint a DATEDIF függvény end_date értékének, ellenkező esetben a #NUM értékre tér vissza! hibaérték.

1.16 Számítsa ki a munkanapokat ünnepnappal vagy anélkül két dátum között

Néha előfordulhat, hogy meg kell számolnia a munkanapok számát két adott dátum között ünnepnapokkal vagy anélkül.

Ebben a részben a NETWORKDAYS.INTL függvényt fogja használni:

Kattints NETWORKDAYS.INTL hogy ismerje érveit és használatát.

Számolja a munkanapokat az ünnepnapokkal

Az A2 és B2 cellában lévő két dátum közötti munkanapok megszámlálásához használja a következő képletet:

=NETWORKDAYS.INTL(A2,B2)

nyomja meg belép kulcs az eredmény eléréséhez.

Számolja a munkanapokat ünnepnapok nélkül

Az A2 és B2 cellában lévő két dátum közötti ünnepnapokkal rendelkező munkanapok számlálásához, a D5:D9 tartományban lévő ünnepnapok figyelmen kívül hagyásához használja a következő képletet:

=NETWORKDAYS.INTL(A5;B5,1;5;D9:DXNUMX)

nyomja meg belép kulcs az eredmény eléréséhez.

Jegyzet:

A fenti képletekben a szombatot és a vasárnapot hétvégének veszik, ha eltérő hétvége van, kérjük, változtassa meg a [hétvége] argumentumot igény szerint.

1.17 Számítsa ki a hétvégéket két dátum között

Ha meg szeretné számolni a hétvégék számát két dátum között, a SUMPRODUCT vagy a SUM függvények jót tehetnek.

A hétvégék (szombat és vasárnap) számlálása két dátum között az A12 és B12 cellában:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))

Or

=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))

nyomja meg belép kulcs az eredmény eléréséhez.

1.18 Konkrét hétköznap kiszámítása két dátum között

Az INT és a WEEKDAY funkciók kombinációja segíthet egy adott hétköznap, például hétfőn két dátum között számolni.

Az A15 és B15 cella az a két dátum, amely között hétfőt szeretne számolni. Kérjük, használja a következő képletet:

=INT((WEEKDAY(A15- 2)-A15 +B15)/7)

nyomja meg belép kulcs az eredmény eléréséhez.

Módosítsa a hét napjának számát a WEEKDAY függvényben, hogy egy másik hétköznapot számoljon:

1 a vasárnap, a 2 a hétfő, a 3 a kedd, a 4 a szerda, az 5 a csütörtök, a 6 a péntek és a 7 a szombat)

1.19 Számolja ki a hátralévő napokat hónapban/évben

Néha érdemes tudni a hónap vagy év hátralévő napjait a megadott dátum alapján, ahogy az alábbi képernyőképen látható:

A hátralévő napok lekérése az aktuális hónapban

Kattints HÓNAP hogy ismerje az érvelést és a használatot.

Az aktuális hónap hátralévő napjainak az A2 cellában való megjelenítéséhez használja a következő képletet:

=EOMONTH(A2,0)-A2

nyomja meg belép gombot, és húzza az automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához, ha szükséges.

Tipp: az eredmények dátumformátumban is megjelenhetnek, csak módosítsa általános vagy számformátumban.

Az aktuális év hátralévő napjainak lekérése

Az aktuális év hátralévő napjainak az A2 cellában való megjelenítéséhez használja a következő képletet:

=DATE(YEAR(A2),12,31)-A2

nyomja meg belép gombot, és húzza az automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához, ha szükséges.

---

1.21 Számítsa ki a két idő közötti különbséget

A két idő közötti különbség megállapításához itt van két egyszerű képlet, amely segíthet.

Tételezzük fel, hogy az A2 és B2 cellában a start_time és a end_time külön-külön tartalmazzák, a következő képletekkel:

=B2-A2

=TEXT(B2-A2,"hh:mm:ss")

nyomja meg belép kulcs az eredmény eléréséhez.

Jegyzet:

• Ha az end_time-start_time paramétert használja, akkor a Cellák formázása párbeszédpanelen szükség szerint formázhatja az eredményt egy másik időformátumra.
• Ha a TEXT(end_time-first_time,"time_format") értéket használja, adja meg azt az időformátumot, amelynél az eredményt meg szeretné jeleníteni a képletben, például a TEXT(end_time-first_time,"h") 16-ot ad vissza.
• Ha a vége_idő kisebb, mint a start_time, mindkét képlet hibaértékeket ad vissza. A probléma megoldásához hozzáadhat ABS-t a képletek elé, például ABS(B2-A2), ABS(SZÖVEG(B2-A2"óó:pp:ss")), majd az eredményt időként formázza.

1.22 Számítsa ki a különbséget két idő között óra/perc/másodperc értékben

Ha az alábbi képernyőképen látható módon szeretné kiszámítani a két idő közötti különbséget órákban, percekben vagy másodpercekben, kövesse ezt a részt.

Szerezzen óra különbséget két idő között

Az A5 és B5 két idő közötti órakülönbség kiszámításához használja a következő képletet:

=INT((B5-A5)*24)

nyomja meg belép gombot, majd formázza az időformátum eredményét általánosra vagy számra.

Ha szeretné megkapni a tizedes óra különbséget, használja a (end_time-start_time)*24-et.

Kapjon perc különbséget két idő között

Az A8 és B8 két idő közötti perckülönbség kiszámításához használja a következő képletet:

=INT((B8-A8)*1440)

nyomja meg belép gombot, majd formázza az időformátum eredményét általánosra vagy számra.

Ha szeretné megkapni a tizedes perc különbséget, használja a (end_time-start_time)*1440 értéket.

Kapjon másodperc különbséget két idő között

Az A5 és B5 két idő közötti másodperces különbség kiszámításához használja a következő képletet:

=(B11-A11)*86400)

nyomja meg belép gombot, majd formázza az időformátum eredményét általánosra vagy számra.

1.23 Csak két idő közötti órakülönbséget számítson ki (nem haladhatja meg a 24 órát)

Ha a két idő közötti különbség nem haladja meg a 24 órát, az ÓRA funkció gyorsan lekéri a két idő közötti órakülönbséget.

Kattints ÓRA további részletekért erről a funkcióról.

Az A14 és B14 cellában lévő idők közötti órakülönbség megtekintéséhez használja a HOUR függvényt a következőképpen:

=HOUR(B14-A14)

nyomja meg belép kulcs az eredmény eléréséhez.

A start_time értéknek kisebbnek kell lennie, mint az end_time, ellenkező esetben a képlet a #NUM! hibaérték.

1.24 Csak két idő között számítsa ki a perc különbséget (nem haladhatja meg a 60 percet)

A PERC funkció gyorsan lekéri az egyetlen perc különbséget e két idő között, és figyelmen kívül hagyja az órákat és másodperceket.

Kattints MINUTE további részletekért erről a funkcióról.

Ha csak az A17 és B17 cellában lévő idők közötti perc különbséget szeretné megjeleníteni, használja a PERC függvényt a következőképpen:

=MINUTE(B17-A17)

nyomja meg belép kulcs az eredmény eléréséhez.

A start_time értéknek kisebbnek kell lennie, mint az end_time, ellenkező esetben a képlet a #NUM! hibaérték.

1.25 Csak két idő között számítsa ki a másodperces különbséget (nem haladhatja meg a 60 másodpercet)

A SECOND funkció gyorsan meg tudja határozni az egyetlen másodperc különbséget e két idő között, és figyelmen kívül hagyja az órákat és perceket.

Kattints MÁSODIK további részletekért erről a funkcióról.

Ha csak az A20 és B20 cellában lévő idők közötti másodperces különbséget szeretné látni, használja a MÁSODIK függvényt a következőképpen:

=SECOND(B20-A20)

nyomja meg belép kulcs az eredmény eléréséhez.

A start_time értéknek kisebbnek kell lennie, mint az end_time, ellenkező esetben a képlet a #NUM! hibaérték.

1.26 Számítsa ki a különbséget a két idő és a visszatérési órák, percek, másodpercek között

Ha a két idő közötti különbséget xx óra xx perc xx másodpercben szeretné megjeleníteni, használja a SZÖVEG funkciót az alábbiak szerint:

Kattints TEXT hogy megértsük ennek a függvénynek az érveit és használatát.

Az A23 és B23 cellában lévő idők közötti különbség kiszámításához használja a következő képletet:

=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").

nyomja meg belép kulcs az eredmény eléréséhez.

Jegyzet:

Ez a képlet is csak a 24 órát meg nem haladó órakülönbséget számítja ki, és az end_time értéknek nagyobbnak kell lennie, mint a start_time, ellenkező esetben a #VALUE értéket adja vissza! hibaérték.

1.27 Két dátum és időpont közötti különbség kiszámítása

Ha két időpont van a mm/nn/éééé óó:pp:ss formátumban, a köztük lévő különbség kiszámításához szükség szerint használhatja az alábbi képleteket.

Kérje le a két dátum és időpont közötti időkülönbséget, és adja vissza az eredményt óó:pp:pp formátumban

Vegyünk két dátumidőt az A2 és B2 cellában példaként, kérjük, használja a következő képletet:

=B2-A2

nyomja meg belép gombot, dátum-idő formátumban adja vissza az eredményt, majd formázza ezt az eredményt a következőre: [H]: mm: ss alatti egyéni kategóriában Szám lap Cellák formázása párbeszéd.
 

Értékelje a két dátum és idő közötti különbséget és a visszatérési napokat, órákat, perceket, másodperceket

Vegyünk két dátumidőt az A5 és B5 cellában példaként, kérjük, használja a következő képletet:

=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "

nyomja meg belép kulcs az eredmény eléréséhez.

Megjegyzés: mindkét képletben az end_datetime értéknek nagyobbnak kell lennie, mint a start_datetime, ellenkező esetben a képletek hibaértékeket adnak vissza.

1.28 Számítsa ki az időkülönbséget ezredmásodpercekkel

Először is tudnod kell, hogyan kell formázni a cellát a milliszekundum megjelenítéséhez:

Jelölje ki azokat a cellákat, amelyeket meg szeretne jeleníteni ezredmásodpercekben, és jobbra a kijelöléshez Cellák formázása hogy engedélyezze a Cellák formázása párbeszédablakban válassza ki szokás a Kategória listát a Szám fül alatt, és írja be ezt óó: mm: ss.000 a szövegmezőbe.

Használjon képletet:

Itt az A8 és B8 cellában lévő két idő közötti különbség kiszámításához használja a következő képletet:

=ABS(B8-A8)

nyomja meg belép kulcs az eredmény eléréséhez.

1.29 Számítsa ki a munkaidőt két dátum között, a hétvégék kivételével

Néha előfordulhat, hogy két dátum között kell számolnia a munkaidőt, kivéve a hétvégéket (szombat és vasárnap).

Itt a munkaidő napi 8 óra, és az A16 és B16 cellában megadott két dátum közötti munkaidő kiszámításához használja a következő képletet:

=NETWORKDAYS(A16,B16) * 8

nyomja meg belép gombot, majd formázza az eredményt általánosra vagy számra.

A két dátum közötti munkaidő kiszámítására vonatkozó további példákért látogasson el ide Szerezzen két óra közötti munkaidőt az Excel programban

---

Ha van Kutools az Excel számára Excelbe telepítve a dátum- és időkülönbség-számítások 90 százaléka gyorsan megoldható anélkül, hogy képleteket kellene emlékezni.

1.31 A Data & Time Helper segítségével kiszámíthatja a két dátum és időpont közötti különbséget

A két dátum és időpont közötti különbség kiszámításához Excelben csak a Dátum és idő segítő elég.

1. Jelöljön ki egy cellát, ahová a számított eredményt helyezi, és kattintson a gombra Kutools > Formula Segítő > Dátum és idő segítő.

2. A durranásban Dátum és idő segítő párbeszédpanelen kövesse az alábbi beállításokat:

1. Jelölje be Különbség választási lehetőség;
2. Válassza ki a kezdő dátumot és a befejezési dátumot Érvek bevitele részben manuálisan is beírhatja a dátumot a beviteli mezőbe, vagy kattintson a naptár ikonra a dátum kiválasztásához;
3. Válassza ki a kimeneti eredmény típusát a legördülő listából;
4. Az eredmény előnézete itt Eredmény szakasz.

3. kettyenés Ok. A kiszámított eredmény megjelenik, és húzza az automatikus kitöltés fogantyúját a szintén kiszámítandó cellák fölé.

Tipp:

Ha meg szeretné kapni a különbséget két dátumidő között, és az eredményt napokban, órákban és percekben szeretné megjeleníteni a Kutools for Excel segítségével, tegye a következőket:

Jelöljön ki egy cellát, ahová az eredményt el szeretné helyezni, és kattintson rá Kutools > Formula Segítő > Dátum idő > Számolja meg a napokat, órákat és perceket két dátum között.

Aztán a Képletek segítője párbeszédpanelen adja meg a kezdő és befejező dátumot, majd kattintson a gombra Ok.

A különbség eredménye pedig napok, órák és percek formájában jelenik meg.

Kattints Dátum és idő segítő hogy többet megtudjon ennek a funkciónak a használatáról.

Kattints Kutools az Excel számára hogy ismerje a bővítmény összes funkcióját.

Kattints ingyenes letöltés hogy megkapja a Kutools for Excel 30 napos ingyenes próbaverzióját

1.32 Hétvége/munkanap/konkrét hétköznapi különbség kiszámítása két dátum között a Formula Helper segítségével

Ha gyorsan meg szeretné számolni a hétvégét, a munkanapokat vagy egy adott hétköznapot két dátum között, a Kutools for Excel Formula Segítő csoport segíthet.

1. Jelölje ki azt a cellát, amelyik elhelyezi a számított eredményt, majd kattintson a gombra Kutools > Statisztikai > Két munkanap között nem munkanapok száma/Két dátum közötti munkanapok száma/Számolja meg a hét adott napjának számát.

2. A kiugróban Képletek segítője párbeszédpanelen adja meg a kezdési és a befejezési dátumot, ha jelentkezik Számolja meg a hét adott napjának számát, meg kell adnia a hét napját is.

Az adott hétköznap megszámlálásához használja az 1-7-es jegyzetet a vasárnap-szombat jelzésére.

3. kettyenés Ok, majd húzza az automatikus kitöltési fogantyút a cellák fölé, amelyeknek meg kell számolniuk a hétvégék/munkanapok/konkrét hétköznapok számát, ha szükséges.

Kattints Kutools az Excel számára hogy ismerje a bővítmény összes funkcióját.

Kattints ingyenes letöltés hogy megkapja a Kutools for Excel 30 napos ingyenes próbaverzióját

---

2. Adja hozzá vagy vonja ki a dátumot és az időt

A két dátum és időpont közötti különbség kiszámításán kívül az összeadás vagy kivonás is a szokásos dátum-idő számítás az Excelben. Például előfordulhat, hogy az esedékesség dátumát a gyártási dátum és a termék eltarthatósági napjai alapján szeretné lekérni.

2.11 Napok hozzáadása vagy kivonása egy dátumhoz

Adott számú nap hozzáadásához vagy kivonásához egy dátumhoz, itt két különböző módszer található.

Tételezzük fel, hogy az A21 cellában lévő dátumhoz 2 napot adunk, válassza ki az alábbi módszerek egyikét a megoldáshoz,

1. módszer dátum+napok

Válasszon ki egy cellát, és írja be a képletet:

=A+21

nyomja meg belép kulcs az eredmény eléréséhez.

Ha 21 napot szeretne kivonni, akkor a pluszjelet (+) mínusz (-) jelre váltja.

2. módszer: Speciális beillesztés

1. Írja be a hozzáadni kívánt napok számát a C2 cellába, majd nyomja meg a gombot Ctrl + C másolni.

2. Ezután válassza ki a 21 napot hozzáadni kívánt dátumokat, kattintson a jobb gombbal a helyi menü megjelenítéséhez, majd válassza a Speciális beillesztés....

3. Ban,-ben Speciális beillesztés párbeszédpanelen ellenőrizze hozzáad opció (Ha napokat szeretne kivonni, jelölje be levon választási lehetőség). Kattintson OK.

4. Most az eredeti dátumok 5 jegyű számokra változnak, és formázza őket dátumként.

2.12 Hónapok hozzáadása vagy kivonása egy dátumhoz

Hónapok hozzáadásához vagy kivonásához egy dátumhoz az EDATE függvény használható.

Kattints EDATE hogy tanulmányozza érveit és használatát.

Tegyük fel, hogy az A6 cellában lévő dátumhoz 2 hónapot adunk, használja a következő képletet:

=EDATE(A2,6)

nyomja meg belép kulcs az eredmény eléréséhez.

Ha 6 hónapot szeretne levonni a dátumból, módosítsa a 6-ot -6-ra.

2.13 Évek hozzáadása vagy kivonása egy dátumhoz

N év hozzáadásához vagy kivonásához egy dátumhoz használható egy képlet, amely kombinálja a DATE, YEAR, MONTH és DAY függvényeket.

Tegyük fel, hogy az A3 cellában lévő dátumhoz 2 évet adunk, használja a következő képletet:

=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))

nyomja meg belép kulcs az eredmény eléréséhez.

Ha 3 évet szeretne levonni a dátumból, módosítsa a 3-at -3-ra.

2.14 Hetek hozzáadása vagy kivonása egy dátumhoz

Hetek hozzáadásához vagy kivonásához egy dátumhoz az általános képlet a következő

Tegyük fel, hogy az A4 cellában lévő dátumhoz 2 hetet adunk, használja a következő képletet:

=A2+4*7

nyomja meg belép kulcs az eredmény eléréséhez.

Ha 4 hetet szeretne levonni a dátumból, módosítsa a pluszjelet (+) mínuszjelre (-).

2.15 Munkanapok összeadása vagy kivonása az ünnepnapokkal együtt vagy azok nélkül

Ebben a részben bemutatja, hogyan használható a WORKDAY funkció a munkanapok hozzáadására vagy kivonására egy adott dátumhoz, az ünnepnapok kivételével vagy a munkaszüneti napok belefoglalásával.

Látogat MUNKANAP hogy további részleteket tudjon meg érveiről és használatáról.

Adjon hozzá munkanapokat, beleértve az ünnepeket is

Az A2 cellában az Ön által használt dátum, a B2 cellában pedig a hozzáadni kívánt napok száma. Kérjük, használja a következő képletet:

=WORKDAY(A2,B2)

nyomja meg belép kulcs az eredmény eléréséhez.

Munkanapok hozzáadása az ünnepnapok kivételével

Az A5 cellában az Ön által használt dátum, a B5 cellában a hozzáadni kívánt napok száma, a D5:D8 tartományban az ünnepnapok listája, kérjük, használja a következő képletet:

=WORKDAY(A5,B5,D5:D8)

nyomja meg belép kulcs az eredmény eléréséhez.

Jegyzet:

A WORKDAY funkció a szombatot és a vasárnapot hétvégének veszi, ha a hétvége szombaton és vasárnap van, akkor használhatja a WOKRDAY.INTL funkciót, amely támogatja a hétvégék megadását.

Látogat WORKDAY.INTL fül alatt találsz.

Ha ki szeretné vonni a munkanapokat egy dátumból, egyszerűen módosítsa a napok számát negatívra a képletben.

2.16 Adott év, hónap, napok hozzáadása vagy kivonása egy dátumhoz

Ha egy adott évet, hónap napokat szeretne hozzáadni egy dátumhoz, akkor a DÁTUM, ÉV, HÓNAP és NAPOK függvényt kombináló képlet jót tehet.

Az A1-ben szereplő dátumhoz 2 év 30 hónap és 11 nap hozzáadásához használja a következő képletet:

=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)

nyomja meg belép kulcs az eredmény eléréséhez.

Ha ki akarja vonni, módosítsa az összes pluszjelet (+) mínusz jelekre (-).

---

2.21 Órák/percek/másodpercek hozzáadása vagy kivonása a dátumidőhöz

Itt talál néhány képletet az órák, percek vagy másodpercek dátumhoz való hozzáadására vagy kivonására.

Órák hozzáadása vagy kivonása a dátumhoz

Tételezzük fel, hogy az A3 cellában lévő dátumhoz 2 órát adunk (ez lehet idő is), használja a következő képletet:

=A2+3/24

nyomja meg belép kulcs az eredmény eléréséhez.

Órák hozzáadása vagy kivonása a dátumhoz

Tételezzük fel, hogy 15 percet adunk a dátumhoz (ez lehet egy idő is) az A5 cellában, kérjük, használja a következő képletet:

=A2+15/1440

nyomja meg belép kulcs az eredmény eléréséhez.

Órák hozzáadása vagy kivonása a dátumhoz

Tételezzük fel, hogy az A20-as cellában 8 másodpercet adunk a dátum-időhöz (ez lehet idő is), kérjük, használja a következő képletet:

=A2+20/86400

nyomja meg belép kulcs az eredmény eléréséhez.

2.22 Összesített idők 24 órán belül

Tegyük fel, hogy van egy Excel táblázat, amely rögzíti az összes alkalmazott munkaidejét egy héten, a teljes munkaidő összegzéséhez a kifizetések kiszámításához használhatja SZUM(tartomány) hogy megkapjuk az eredményt. De általában az összegzett eredmény 24 órát meg nem haladó időként jelenik meg, ahogy az alábbi képernyőképen látható, hogyan kaphatja meg a helyes eredményt?

Valójában csak formázni kell az eredményt [óó]:mm:ss.

Kattintson a jobb gombbal az eredmény cellára, válassza ki Cellák formázása a helyi menüben és a felbukkanó menüben Cellák formázása párbeszédablakban válassza ki szokás a listáról, és írja be [óó]:mm:ss kattintson a jobb oldali szövegmezőbe OK.
  

Az összesített eredmény helyesen jelenik meg.

2.23 Munkaidő hozzáadása egy dátumhoz a hétvége és az ünnepnapok kivételével

Itt található egy hosszú képlet a befejezési dátum meghatározásához, amely egy adott számú munkaórának a kezdő dátumhoz való hozzáadásával történik, és kizárja a hétvégéket (szombat és vasárnap) és az ünnepnapokat.

Az Excel táblázatban az A11 a kezdési dátumot, a B11 a munkaidőt, az E11 és az E13 cellában a munka kezdési és befejezési időpontját, az E15 cellában pedig a kihagyandó ünnepnapot tartalmazza.

Kérjük, használja a következő képletet:

=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))

nyomja meg belép kulcs az eredmény eléréséhez.

---

Ha telepítette a Kutools for Excel programot, csak egy eszköz - Dátum és idő Helper meg tudja oldani a legtöbb számítást a dátumidő összeadásával és kivonásával.

1. Kattintson arra a cellára, amelybe ki szeretné adni az eredményt, és a gombra kattintva alkalmazza ezt az eszközt Kutools > Formula Segítő > Dátum és idő segítő.

2. Ban,-ben Dátum és idő segítő párbeszédpanelen ellenőrizze hozzáad opció vagy levon opciót, majd válassza ki a cellát, vagy írja be közvetlenül a használni kívánt dátumot Érvek bevitele szakaszt, majd adja meg a hozzáadni vagy kivonni kívánt éveket, hónapokat, heteket, napokat, órákat, perceket és másodperceket, majd kattintson Ok. Lásd a képernyőképet:

Megtekintheti a számított eredményt a Eredmény szakasz.

Most az eredmény megjelenik, húzza az automatikus fogantyút a többi cellára az eredmények eléréséhez.

Kattints Dátum és idő segítő hogy többet megtudjon ennek a funkciónak a használatáról.

Kattints Kutools az Excel számára hogy ismerje a bővítmény összes funkcióját.

Kattints Díj letöltése hogy megkapja a Kutools for Excel 30 napos ingyenes próbaverzióját

---

2.41 Ellenőrizze vagy jelölje ki, ha egy dátum lejárt

Ha van lista a termékek lejárt dátumairól, érdemes ellenőrizni és kiemelni azokat a dátumokat, amelyek a mai napon lejártak, ahogy az alábbi képernyőképen látható.

Valójában a Feltételes formázás gyorsan tudja kezelni ezt a munkát.

1. Válassza ki az ellenőrizni kívánt dátumokat, majd kattintson a gombra Kezdőlap > Feltételes formázás > Új szabály.

2. Ban,-ben Új formázási szabály párbeszédablakban válassza ki Képlet segítségével határozza meg, hogy mely cellákat formázza a Válasszon egy szabálytípust szakasz és típus =B2 be a beviteli mezőbe (B2 az első ellenőrizni kívánt dátum), és kattintson a gombra kialakított felbukkanni Cellák formázása párbeszédpanelen, majd válasszon másik formázást, hogy megfeleljen a lejárt dátumoknak. Kattintson OK > OK.
  

2.42 Visszaküldi az aktuális hónap végét/a következő hónap első napját/a>

Egyes termékek lejárati dátuma a gyártási hónap végén vagy a következő gyártási hónap első napján van, a lejárt dátumok gyártási dátum alapján történő gyors listázásához kérjük, kövesse ezt a részt.

Az aktuális hónap vége

Itt van egy gyártási dátum a B13 cellában, kérjük, használja a következő képletet:

=EOMONTH(B13,0)

nyomja meg belép kulcs az eredmény eléréséhez.

Kapja meg a következő hónap 1. napját

Itt van egy gyártási dátum a B18 cellában, kérjük, használja a következő képletet:

=EOMONTH(B18,0)+1

nyomja meg belép kulcs az eredmény eléréséhez.

---

3. Számítsa ki az életkort

Ebben a részben az életkor egy adott dátum vagy sorozatszám alapján történő kiszámításának megoldási módszereit sorolja fel.

---

3.11 Számítsa ki az életkort az adott születési dátum alapján

Adja meg az életkort tizedes számmal a születési dátum alapján

Kattints ÉVFRAC érveivel és használatával kapcsolatos részletekért.

Ha például a B2:B9 oszlopban található születési dátumok listája alapján szeretné megkapni az életkorokat, használja a következő képletet:

=YEARFRAC(B2,TODAY())

nyomja meg belép gombot, majd húzza lefelé az automatikus kitöltés fogantyúját, amíg az összes életkort ki nem számítja.

Tipp:

1) A tizedesjegyet igény szerint megadhatja a Cellák formázása párbeszéd.

2) Ha az életkort egy adott napon egy adott születési dátum alapján szeretné kiszámítani, módosítsa a TODAY() értéket a konkrét dátumra, dupla idézőjelekkel, például =YEARFRAC(B2,"1/1/2021")

3) Ha a következő év életkorát a születési dátum alapján szeretné megkapni, adjon hozzá 1-et a képlethez, például =YEARFRAC(B2,TODAY())+1.

Adja meg az életkort egész számmal a születési dátum alapján

Kattints DATEDIF érveivel és használatával kapcsolatos részletekért.

A fenti példával a B2:B9 listában szereplő születési dátumok alapján az életkor meghatározásához használja a következő képletet:

=DATEDIF(B2,TODAY(),"y")

nyomja meg belép gombot, majd húzza lefelé az automatikus kitöltés fogantyúját, amíg az összes életkort ki nem számítja.

Tipp:

1) Ha egy adott dátumon szeretné kiszámítani az életkort egy adott születési dátum alapján, módosítsa a TODAY() paramétert a konkrét dátumra, dupla idézőjelekkel, például =DATEDIF(B2,"1/1/2021","y") .

2) Ha a következő év életkorát a születési dátum alapján szeretné megkapni, csak adjon hozzá 1-et a képlethez, például =DATEDIF(B2,TODAY(),"y")+1.

3.12 Számítsa ki az életkort években, hónapban és napokban, adott születésnap alapján

Ha az életkort egy adott születési dátum alapján szeretné kiszámítani, és az eredményt xx év, xx hónap, xx nap formájában szeretné megjeleníteni, ahogy az alábbi képernyőképen látható, akkor itt egy hosszú képlet segíthet.

Ha a B12 cellában lévő születési dátum alapján szeretné megkapni az életkort években, hónapokban és napokban, használja a következő képletet:

=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"

nyomja meg belép gombot az életkor meghatározásához, majd húzza le az automatikus kitöltés fogantyúját a többi cellára.

Tipp:

Ha az életkort egy adott napon egy adott születési dátum alapján szeretné kiszámítani, módosítsa a TODAY() paramétert a konkrét dátumra, dupla idézőjelekkel, például = =DATEDIF(B12,"1/1/2021","Y")& " Évek, "&DATEDIF(B12,"1/1/2021","YM")&" hónapok, "&DATEDIF(B12,"1/1/2021","MD")&"napok".

3.13 Az életkor kiszámítása 1. 1. 1900. előtti születési dátum alapján

Az Excelben az 1. előtti dátumot nem lehet dátum-időként megadni, és nem lehet megfelelően kiszámítani. De ha a megadott születési dátum (1/1900 előtti) és halálozási dátum alapján szeretné kiszámolni egy híres ember életkorát, akkor csak egy VBA kód segíthet.

1. nyomja meg más + F11 gombokat az engedélyezéshez Microsoft Visual Basic for Applications ablakot, és kattintson rá betétlap lapot és válasszon Modulok új modul létrehozásához.

2. Ezután másolja ki és illessze be az alábbi kódot az új modulba.

VBA: Számolja ki az életkort 1/1/1900-je előtt

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. Mentse el a kódot, térjen vissza a lapra, és válasszon ki egy cellát a kiszámított életkor elhelyezéséhez, írja be: =AgeFunc(születési dátum,halálozási dátum), ebben az esetben =AgeFunc(B22,C22), nyomja meg az Enter billentyűt az életkor meghatározásához. És használja az automatikus kitöltési fogantyút a képlet más cellákra való alkalmazásához, ha szükséges.

Ha van Kutools az Excel számára Excelbe telepítve alkalmazhatja a Dátum és idő segítő eszköz az életkor kiszámításához.

1. Jelöljön ki egy cellát, amelybe el szeretné helyezni a számított életkort, és kattintson a gombra Kutools > Formula Segítő > Dátum és idő segítő.

2. Ban,-ben Dátum és idő segítő párbeszéd,

• 1) Ellenőrizze Kor választási lehetőség;
• 2) Válassza ki a születési dátum cellát, vagy írja be közvetlenül a születési dátumot, vagy kattintson a naptár ikonra a születési dátum kiválasztásához;
• 3) Válasszon Ma opciót, ha az aktuális életkort szeretné kiszámítani, válassza a Meghatározott dátum opciót, és adja meg a dátumot, ha a múltban vagy a jövőben szeretné kiszámítani az életkort;
• 4) Adja meg a kimenet típusát a legördülő listából;
• 5) Tekintse meg a kimeneti eredményt. Kattintson Ok.

Kattints Dátum és idő segítő hogy többet megtudjon ennek a funkciónak a használatáról.

Kattints Kutools az Excel számára hogy ismerje a bővítmény összes funkcióját.

Kattints ingyenes letöltés hogy megkapja a Kutools for Excel 30 napos ingyenes próbaverzióját

---

3.31 Születésnap megszerzése az azonosító számból

Ha létezik olyan azonosítószámok listája, amelyek az első 6 számjegyet használják a születési dátum rögzítésére, például a 920315330 azt jelenti, hogy a születési dátum 03., hogyan lehet gyorsan átvinni a születési dátumot egy másik oszlopba?

Most vegyük példának a C2 cellában kezdődő azonosítószámok listáját, és használjuk a következő képletet:

=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)

nyomja meg belép kulcs. Ezután húzza le az automatikus kitöltési fogantyút, hogy további eredményeket kapjon.

Jegyzet:

A képletben a hivatkozást igény szerint módosíthatja. Például, ha az azonosítószám 13219920420392, a születésnap 04., akkor módosíthatja a képletet a következőre: =MID(C20)&"/"&MID(C1992)&"/ "&MID(C2,8,2;2,10,2;2,4,4) a helyes eredmény eléréséhez.

3.32 Számítsa ki az életkort az azonosítószámból

Ha létezik olyan azonosítószámok listája, amelyek az első 6 számjegyet használják a születési dátum rögzítésére, például a 920315330 azt jelenti, hogy a születési dátum 03., hogyan lehet gyorsan kiszámítani az életkort az egyes azonosítószámok alapján az Excelben?

Most vegyük példának a C2 cellában kezdődő azonosítószámok listáját, és használjuk a következő képletet:

=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")

nyomja meg belép kulcs. Ezután húzza le az automatikus kitöltési fogantyút, hogy további eredményeket kapjon.

Jegyzet:

Ebben a képletben, ha az év kisebb, mint az aktuális év, akkor az év 20-zal kezdődőnek minősül, például a 200203943 a 2020-as év; ha az év nagyobb, mint az aktuális év, akkor az év 19-cel kezdődőnek minősül, például a 920420392 1992-es évnek minősül.

---

További Excel oktatóanyagok: