3 egyszerű módszer a dátumok kivonására az Excelben

Egy bizonyos ponton, amikor Excelben dolgozik, meg kell találnia a két dátum közötti napok számát.

Szerencsére az Excel számos módot kínál a dátumok kivonására, hogy kiszámítsa a napok közötti különbséget.

Három egyszerű módja van a dátumok kivonásának az Excelben:

1. Egyszerű kivonás
2. A DAYS() függvény használata
3. A NETWORKDAYS() függvény használata

Van egy régebbi módszer, a DATEDIF() függvény, amelyet a Microsoft már nem támogat. A felsorolt ​​három módszer azonban megbízható és pontos. Gyorsan megtalálja a dátumok közötti különbségeket olyan feladatoknál, mint például a projektek ütemezése vagy az üzleti elemzések.

A cikk végére az egyes módszereket saját munkájában is alkalmazhatja a dátumok kivonására, így könnyen kiszámíthatja a napok számát.

Menjünk bele.

Tartalomjegyzék

• A dátumok megértése Excelben
• 1. Egyszerű kivonás segítségével kapja meg a napok számát
  • Egyéni címke hozzáadása
• 2. A DAYS függvény használata a napok számának kiszámításához
• 3. A NETWORKDAYS funkció használata
  • Ünnepnapok megadása
  • Különböző napok megadása a hétvégékhez
• A régebbi DATEDIF függvény (nem ajánlott)
• Az aktuális dátum használata a számításokban
• Hogyan kell kivonni hónapokat vagy éveket egy nap megtalálásához
• Mi a teendő, ha a kezdő dátum a befejező dátum után van?
• Tippek a pontos dátumkivonáshoz
  • Tipp 1. Használja a megfelelő képletet
  • Tipp 2. Formázza helyesen a dátumokat
  • 3. tipp. Számoljon szökőévekkel
  • Tipp 4. Jelenítse meg egyértelműen a dátumokat és az eredményeket
• Gyakori hibák és hibaelhárítás
  • 1. hiba: #ÉRTÉK!
  • 2. hiba: hibás eredmények
• Következtetés

A dátumok megértése Excelben

A dátumok az Excelben néha zavaróak lehetnek, mivel számokként és szövegként is működnek. Ésszerűbb, ha megérti, hogy a dátumokat sorozatszámként tárolja.

Az Excel dátumrendszere 1900. január 1-től kezdi meg a számolást, amelyhez az 1-es sorszám tartozik. Minden ezt követő naphoz hozzárendeli a következő számot.

Például 1900. január 2-nak a sorszáma 2.

Valószínűleg láthatja, hogy így egyszerűbbé válik két dátum kivonása és a közöttük lévő napok megtalálása. Fussunk végig az erre javasolt módokon.

Azt javaslom, hogy indítson el egy új munkafüzetet. Minden példa, amit adok, egy gyors bemutató.

A munkalapom a B2 cellát és a C2 cellát használja a minta dátumaihoz. A példákat sablonként használhatja a munkájához.

1. Egyszerű kivonás segítségével kapja meg a napok számát

Az első módszer a két dátum közötti napok számának meghatározására az egyszerű kivonás (a mínusz jel). Ez a formátum:

=End-Start

Kovesd ezeket a lepeseket:

1. Írja be a két dátumot külön cellákba.
2. Egy új cellába írjon egy ilyen képletet:=C2-B2
3. Nyomd meg az Entert.

A kapott érték a két dátum közötti teljes napok száma lesz.

Példánkban a B2-be „2023. március 3.”, a C2-es cellába pedig „2023. március 20.”-t írtam be. A számítás eredménye 17 lett.

Az alábbi ábrán látható A2 cella egyszerűen az alkalmazott módszer leírása.

Egyéni címke hozzáadása

Ha egyéni címkével szeretné megjeleníteni az eredményt, használja a következő képletet:

=C2-B2 & " napok"

Ez a képlet összefűzi a cellahivatkozások számított dátumkülönbségét a "napok" szöveggel. Ez leíróbb eredményt ad.

Például, ha 17 nap van a két dátum között, az eredmény „17 nap” lesz.

2. A DAYS függvény használata a napok számának kiszámításához

A DAYS Excel függvény egy beépített dátumfüggvény, amely kiszámítja a dátumok közötti napokat. Ez a formátum:

=NAP(vége, kezdete)

Kovesd ezeket a lepeseket:

1. Írja be a két dátumot külön cellákba.
2. Egy új cellába írjon egy ilyen képletet:=DAYS(C2, B2)
3. Nyomd meg az Entert.

A kapott érték a két dátum közötti teljes napok száma lesz.

Az előző példában szereplő dátumokat használtam a függvény teszteléséhez. A kapott szám ugyanaz volt.

Az eredmény mindig egész szám lesz. Ha azt szeretné, hogy tizedesjegyként jelenjen meg (a záró nullákkal), használja a formázás fület a számformátum módosításához.

3. A NETWORKDAYS funkció használata

A NETWORKDAYS dátum függvény kiszámítja a két dátum közötti munkanapok számát. Mivel munkanapokról van szó, a számítás nem tartalmazza a hétvégéket és a meghatározott ünnepnapokat.

Ez a formátum:

= HÁLÓZATNAPOK (kezdet, vége, [ünnepnapok])

A harmadik paraméter (Ünnepnapok) nem kötelező. Ha nem használja, a képlet kizárja a hétvégéket.

Kövesse az alábbi lépéseket a legegyszerűbb képlet használatához ünnepnapok megadása nélkül:

1. Írja be a két dátumot külön cellákba.
2. Egy új cellába írjon be egy ilyen képletet:=NETWORKDAYS(B2, C2)
3. Nyomd meg az Entert.

Az előző példában használt dátumokat (2023. január 3. és január 20.) figyelembe véve az Excel függvény eredménye 14.

Ez három nappal kevesebb, mint az egyszerű kivonás. Ez azért van, mert két hétvége van ezalatt az időkeretben (kétszer ellenőriztem).

Ünnepnapok megadása

Megadhatja a képletnek a számításból kizárandó ünnepnapok listáját.

Ezt úgy teheti meg, hogy minden ünnepnapot beír egy cellatartományba, és beírja a tartományt a Holiday paraméterébe.

Ezt könnyebb megérteni egy működő példával.

1. A két dátum a B2 és C2 cellában található.
2. Az első ünnep dátuma a D2 cellában, a második pedig az E2 cellában található.
3. Az F2 cellában használja a következő képletet:=NETWORKDAYS(B2, C2, D2:E2)
4. Nyomja meg az Enter billentyűt az eredmény megtekintéséhez.

Példánkban az eredmény 12. Ez két nappal kevesebb, mint az előző számításnál, mert a képlet megtalálta a dátumok listáját az általunk megadott cellatartományban.

( A képen nem jelenítem meg az A1 és A2 cellát. Az A1 cella üres, és az A2 tartalmazza a leírást. ).

Egyébként, ha hosszú listája van az ünnepnapok dátumairól, és nem biztos benne, hogy szerepel-e ismétlődés, az alábbi videó megmutatja, hogyan ellenőrizheti az adatokat.

Azt is megmutatja, hogyan távolíthatja el a másolatokat!

Különböző napok megadása a hétvégékhez

Vegye figyelembe, hogy a NETWORKDAYS a szombatot és a vasárnapot használja alapértelmezett hétvégi napként a számításhoz.

Ha a hétvégéi eltérőek, a NETWORKDAYS.INTL funkcióval testreszabhatja a hétvége napját.

Ha többet szeretne megtudni a formátumról, itt találja a Microsoft dokumentációját .

A régebbi DATEDIF függvény (nem ajánlott)

A Microsoft Excel jelenlegi verziójában nem találja meg a DATEDIF függvényt a beépített függvények listájában.

Használata azonban továbbra is lehetséges. A Microsoft nem sorolja fel, mert a cég speciális esetek kivételével már nem támogatja a funkciót .

A probléma az, hogy nem mindig pontos. Ezért javaslom, hogy kerülje el ezt a funkciót.

Leírom, ha a szoftver régebbi verzióját használja. Előfordulhat, hogy a régebbi sablonokban is megtalálja, amelyekkel dolgozik. A szintaxis a következő:

= DATEDIF(kezdet, vége, "d")

Ha a harmadik paraméterben „d”-t használ, a függvény napokban számítja ki. Ügyeljen arra, hogy a „d” jelet dupla idézőjelbe tegye.

Alternatív felhasználásként az „m” a hónapok számát, míg az „y” az évek számát adja meg.

Íme a lépések:

1. Írja be a két dátumot a szomszédos cellákba.
2. Egy új cellába írja be a következő képletet: =DATEDIF(Start_Date, End_Date, "d").
3. Nyomd meg az Entert.

Ezzel visszaadja a két dátum közötti napokat.

A korábbi példáink celláit használva a függvény a következő: DATEDIF(B2, C2, “d”).

A többi függvénytől eltérően ez a #NUM! hiba, ha az első randevú a második után van.

Az aktuális dátum használata a számításokban

Ha azt szeretné, hogy a számításban az egyik dátum legyen az aktuális dátum, akkor a praktikus Today funkciót kínálja.

Egyszerűen cserélje ki az egyik cellahivatkozást a TODAY()-re. A munkalapomon csak a B oszlopra lenne szükségem a dátumok tárolására. Íme egy példa:

=NAPOK(MA(), B2)

Kissé zavaró, hogy a VBA egyenértékű függvényének más neve van. Használja a Date() függvényt, ha makrót kódol.

Hogyan kell kivonni hónapokat vagy éveket egy nap megtalálásához

Ha pontosan egy hónappal ezelőtti dátumot keres, akkor kísértést érezhet, hogy levonja a 30 napot a TODAY() függvény kimenetéből.

Ez azonban csak hónapok egy részében lesz pontos, így nem jó módszer.

Az EDATE funkció pontosan erre a célra szolgál. A szintaxis a következő:

= EDATE(kezdet, hónap)

A pontosan egy hónappal ezelőtti konkrét példámhoz a következőket használnám:

=EDÁTUM(ma(), -1)

Ez azt jelenti, hogy a dátum egy teljes hónappal a múltban van. Ez a legjobb módja a hónapok kivonásának.

Ha ki kell vonni az éveket, az YEAR függvény egy adott dátumtól számítja az éveket.

Mi a teendő, ha a kezdő dátum a befejező dátum után van?

Ha az első dátum nagyobb, mint a második, a javasolt módszerek ezt nem fogják hibának tekinteni. Elvégzik a számítást, és negatív számot adnak.

Például, ha tévedésből megváltoztattam a dátumokat a példákban, az eredmény -17.

Mindig jó ötlet, hogy megakadályozzuk magunkat a hibák elkövetésében. Használhatja az IF függvényt az érvénytelen dátumtartomány ellenőrzésére, és helyette hibaüzenetet jeleníthet meg.

Íme egy példa az IF függvény és a DAYS függvény beépítésére:

=HA(B2 > C2, "Érvénytelen dátumok", NAPOK(B2, C2, "d"))

Ez a képlet azt ellenőrzi, hogy az első dátum nagyobb-e, mint a második. Ha ez a helyzet, megjelenik az „Érvénytelenítés dátuma” szöveg.

Ellenkező esetben a különbséget napokban számítja ki, mint korábban.

Az alábbi példában felcseréltem a két dátumot. A függvény megjeleníti az általam az IF() függvényhez megadott szöveget.

Tippek a pontos dátumkivonáshoz

Amikor dátumokkal dolgozik a nyelvben, fontos, hogy biztosítsa a számítások pontosságát.

Íme a legjobb tippjeim a dátumszámítások használatához.

Tipp 1. Használja a megfelelő képletet

Azt javaslom, hogy ne használja a DATEDIF() függvényt. Nem mindig megbízható.

Ehelyett válasszon egyet a cikkben ismertetett három másik módszer közül.

Tipp 2. Formázza helyesen a dátumokat

Győződjön meg arról, hogy a kezdő és a záró dátum cella dátumként van formázva.

Ehhez kövesse az alábbi lépéseket:

1. kattintson a jobb gombbal a cellára.
2. válassza a „Cellák formázása” lehetőséget (vagy nyomja meg a ctrl + 1 billentyűkombinációt a párbeszédpanel megnyitásához).
3. válassza a „Dátum” lehetőséget a „Szám” fül alatt.

Ez segít elkerülni az esetleges hibákat a számításokban.

3. tipp. Számoljon szökőévekkel

Ne feledje, hogy a szökőévek befolyásolhatják, hogy hány nap van két dátum között.

Az Excel automatikusan figyelembe veszi a szökőéveket a dátumszámításaiban, így ha a megfelelő képletet és formázást követi, pontos eredményeket kell kapnia.

A szökőévek listáját itt találja .

Tipp 4. Jelenítse meg egyértelműen a dátumokat és az eredményeket

Az adatok és eredmények olvashatóságának javítása érdekében fontolja meg az Excel néhány formázási funkciójának használatát:

• Állítsa be az oszlopszélességet a tartalom szélességének megfelelően.
• Igazítsa a dátumadatokat az egyes cellák közepére.
• Alkalmazzon stílusokat (például félkövér vagy dőlt), hogy jobban megkülönböztethetővé tegye az oszlopfejléceket.

Gyakori hibák és hibaelhárítás

Amikor a dátumokkal dolgozik az Excelben, előfordulhat, hogy néhány gyakori hibát észlel. Íme néhány hibaelhárítási tipp, amelyek segítenek megoldani ezeket a problémákat:

1. hiba: #ÉRTÉK!

Ez a hiba akkor fordul elő, ha a cellaértékeket a rendszer nem ismeri fel megfelelő dátumként.

A probléma megoldása:

• Győződjön meg arról, hogy mindkét dátumcella dátumként van formázva.
• Ellenőrizze a területi beállításokat a dátumok helyes értelmezéséhez.

A különböző nyelvek eltérő dátumelrendezéssel rendelkeznek (pl. nn/hh/yy vagy mm/nn/yy).

2. hiba: hibás eredmények

Ha helytelen eredményeket kap, vegye figyelembe a következőket

• Ellenőrizze még egyszer, hogyan adta meg a dátumokat. A hónap és a nap összekeverése számítási hibákat okozhat.
• Használja a hálózati napokat, ha csak a munkanapokat szeretné két dátum között számolni a NETWORKDAYS() függvény használatával.
• A félreértések és hibák elkerülése érdekében következetesen ugyanazt a dátumformázási módot használja a munkalapon.
• Kerülje el a kézi számításokat az ebben a cikkben ismertetett módszerekkel.

Következtetés

Ebben a cikkben különféle módszereket vizsgáltunk meg a dátumok Excelben való kivonására és a két dátum közötti napok közötti különbség kiszámítására.

Mostanra már biztos lehet abban, hogy a DAYS függvényt, a NETWORKDAYS függvényt és az egyszerű kivonást használja a pontos eredmények elérése érdekében.

Ne felejtse el mindig kétszer ellenőrizni adatait, mivel a helytelen vagy érvénytelen formátumok váratlan eredményekhez vezethetnek.

A tárgyalt módszerek bármelyikének alkalmazásakor ügyeljen arra, hogy mind a kezdő dátum, mind a befejezés dátuma a megfelelő formátumban legyen megadva, hogy az Excel felismerje, mik azok.

Most, hogy rendelkezik ezzel a tudással, többek között hatékonyan nyomon követheti a projektek időtartamát, elemezheti a trendeket és kezelheti a határidőket.

Gyakoroljon és kísérletezzen a különböző dátumformátumokkal és -funkciókkal, hogy kiaknázhassa az Excel dátumozási képességeiben rejlő lehetőségeket. Jó számolgatást!

Készen áll arra, hogy Excel-készségeit a következő szintre emelje? Nézze meg a mi