Office Guru

Műveletek 1900 előtti dátumokkal Excelben

2016. március 11. - Office Guru

Egyik kedves Olvasóm igencsak fogós fejtörővel örvendeztetett meg a napokban és csak egy igazi örömöt okozó, kiadós elmélkedés után született meg a megoldás, ami természetesen, ahogy más Excel-problémáknál, úgy valószínűleg most sem az egyetlen lehetséges megoldás, de ez egy tényleg jó kis feladvány, szóval szokás szerint nyitott vagyok más megoldási módszerekre is.

Maga a kérdés több témakört ölel fel, de a probléma középpontjában a dátumok és az Excel dátumkezelése áll, hiszen alapvetően közismert, hogy a táblázatkezelő az 1900-as és az 1904-es dátumrendszert tudja használni, azaz az egyiknél 1900. január elseje jelenti az egyes sorszámot, a másiknál pedig 1904. január elseje.

Amiért ez igen fájó pont az az, hogy bár formátum és bevitel szempontjából az Excel képes kezelni 1900 előtti dátumokat is, kalkulációkat már nem tudunk velük végezni:

ss01.jpgPersze ez nem egy mindennap felmerülő igény, az tény, de ettől függetlenül mi van, ha régi történelmi személyiségek pontos életkorát szeretnénk napban megadni születésük és haláluk időpontja alapján? Vagy teljesen más okból lenne erre szükségünk?

Anno itt írtam az egyik rejtélyes Excel-formuláról, a DATEDIF-ról, amely önmagában most sem tud segíteni nekünk, hiszen ha 1900 előtti dátumokat adunk be paraméterként, akkor ez a függvény is egy vaskos #VALUE! hibaüzenettel válaszol, de ha kicsit kreatívak vagyunk, akkor mégis el tudjuk érni, hogy megkapjuk a kívánt értéket.

Adott tehát egy egyszerű kis feladat:

ss02.jpgAlapból itt most fejből is meg tudjuk mondani az eredményt, de a cél az, hogy bármely dátum és bármennyi dátum esetén működjön ez a logika. Összesen három függvényt fogunk felhasználni a különbség meghatározásához, a fentebb már említett DATEDIF formula mellett egy LEN és egy RIGHT is kelleni fog majd - plusz az, hogy a dátumok egységes formátumban legyenek (ezen függvényekről hosszabban nem írok már, elég sok posztban bemutattam őket, úgyhogy valószínűleg minden Olvasó előtt ismertek).

Az én példámban szereplő formátum esetén első lépésként vegyük le az utolsó négy karaktert a dátumról:

=LEFT(K3,LEN(K3)-4)

Mit is teszünk? A K3 cellában szereplő értékből levágjuk az érték karakterszáma-4 értéket, azaz konkrétan az évet (hiszen ez pont négy karakter az értékünk végén):

ss03.jpgMajd újra hozzátesszük az évet, de nem ugyanúgy, hanem a dátumaink tartományát figyelembe véve hozzáadunk még több száz évet:

=LEFT(K3,LEN(K3)-4)&RIGHT(K3,4)+400

Az előbb levágott, most már év nélküli részhez hozzáadjuk teljes eredeti dátumunk utolsó négy karakterét, tehát az évet (RIGHT(K3,4)), megnövelve 400-zal:

ss04.jpgEzt teljesen ugyanezzel a módszerrel megismételjük a másik dátumra is:

=LEFT(L3,LEN(L3)-4)&RIGHT(L3,4)+400

Ezután pedig szimplán csak beágyazzuk a két, most már jelentősen jövőbeni dátumot egy DATEDIF függvénybe és meg is van a különbségünk:

=DATEDIF(LEFT(K3,LEN(K3)-4)&RIGHT(K3,4)+400,LEFT(L3,LEN(L3)-4)&RIGHT(L3,4)+400,"d")

Mint tudjuk, DATEDIF függvényünk első két paramétere a két dátum, amelynek különbségét meg akarjuk határozni, harmadik paramétere pedig az az indikátor, amellyel jelezzük, hogy napban, hónapban, évben stb. szeretnénk látni a különbséget.

ss05.jpgEz biztosan nem a legszebb megoldás, de elvileg olyan makró nélküli eredmény, amire megbízhatóan támaszkodhatunk - ennek ellenére további ötleteket, javaslatokat szívesen fogadok.

Update: az évszámhoz kizárólag 400-at illetve többszörösét adhatjuk hozzá a szökőévek okán. Köszönet az észrevételért.

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr618466436

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

Jozsef · http://road2boston.blog.hu 2016.03.12. 15:44:33

Sajnos, a cikkben van egy hiba: az évek számához nem 500-t kell hozzáadni. Könnyen ellenőrizhetjük, ha megnézzük, hogy pl. 2000.01.31-2000.03.15 és 2500.01.31-2500.03.15. esetén 44 ill. 43 nap a különbség.
A szökőévek tulajdonságai miatt , 400 valamilyen többszörösét kell hozzáadni , én legegyszerűbbnek a 2000-t találom, mert akkor csak az év első számjegyét kell módosítani. Természetesen, összes Excel dátumszámítás csak a Gergely-naptár bevezetése (1582.10.15) utáni dátumokra lesz értelmes.
A DATEDIF magyar változata DÁTUMTÓLIG valamilyen rejtélyes okból (ha jól emlékszem Excel 2003 óta) nem jelenik a Függvény beszúrás függvényei között, ezért kézzel kell beírni. DÁTUMTÓLIG(kezdődátum;végdátum;különbség_tipus) formában, ahol a különbség_típus "d"-nap, "m"- teljes hónap, "y" teljes -év, "ym"- törtéven belüli hónap, "yd" -törtéven belüli nap, "md" -törthónapon belüli nap.

Office Guru 2016.03.12. 19:15:09

@Jozsef: Oppá, ezt tényleg elnéztem, köszi az észrevételt, javítottam. is.

DATEDIF függvényről pedig már írtam a cikkben is belinkelt írásban.

motamas 2022.09.20. 16:57:27

Kedves Office Guru!
Segítséget szeretnék kérni ezzel a témakörrel kapcsolatban. Adott egy 1900.01.01 előtti dátum és időszak napokban megadva. Hogyan tudom kivonni a dátumból a napok számát? Pl. 1875.07.16 - 188 nap =?
Segítségét előre is köszönöm.
süti beállítások módosítása