Funkce DATEDIF je zajímavou a očím mnoha uživatelů skrytou funkcí, která slouží ke počítání rozdílu mezi dvěma daty. Tuto funkci však nenalezneme v seznamu funkcí ani v nápovědě kvůli soudní při se společností IBM, ale je v Excelu přístupná pro zajištění kompatibility s aplikací Lotus 1-2-3.
Funkci je možné využít při potřebě zjištění rozdílu mezi dvěma daty, a to ve dnech, měsících nebo letech, přičemž funkce DATEDIF umí počítat i s jednotlivými jednotkami bez ohledu na jiné (např. spočítat, jaký je rozdíl mezi dvěma daty v měsících, ale bez ohledu na roky).
=DATEDIF(Date1Date2;Interval)
Date1 je počáteční datum, od kterého chceme počítat rozdíl.
Date2 je konečný datum, konec období, se kterým počítáme. Argument Date2 nemůže být nižší (starší datum) než Date1 - jinak vrátí funkce chybovou hodnotu #NUM!.
Interval specifikuje, v jakých jednotkách požadujeme výsledek. Argument interval se přímo ve funkci vždy zadává v uvozovkách, na velikosti písmen nezáleží - např. =DATEDIF("17.11.1989";DNES();"M"). Pokud bychom se v argumentu Interval odkazovali na buňku, ve které je argument specifikován, pak se adresa buňky do uvozovek nezapisuje - např. =DATEDIF("17.11.1989";DNES();"M" nebo =DATEDIF("17.11.1989";DNES();B1).
| Interval | Význam |
|---|---|
| "D" | Počet dnů mezi dvěma daty |
| "M" | Počet celých kalendářních měsíců mezi dvěma daty |
| "Y" | Počet celých kalendářních roků mezi dvěma daty |
| "MD" | Počet dnů mezi dvěma daty, počet měsíců i let je ignorován |
| "YD" | Počet dnů mezi dvěma daty, počet let je ignorován |
| "YM" | Počet kalendářních měsíců mezi dvěma daty, počet dnů a roků je ignorován |
Určité problémy mohou nastat při práci s přestupným rokem, kdy Excel rozhoduje, zda zařadit i 29. únor podle roku počátečního data. Problémem je, že pokud je rok počátku přestupný, považuje i rok v koncovém datu za přestupný. O této problematice se lze často hojně dočíst na internetu a jedná se o největší chybu této velmi užitečné funkce.
1) Pomocí funkce DATEDIF zjistíme počet dní mezi dvěma daty. Ve sloupci A je počátek intervalu, ve sloupci B konec. Pro tento výpočet můžeme použít buď funkci DATEDIF s použitím argumentu "D" nebo jednoduše odečíst počáteční datum od koncového (datum v ExceluExcel pracuje s daty na bázi pořadových čísel, kde každému datu od 1. 1. 1900 jsou přidělena čísla od jedné dále. Hodiny, minuty a sekundy jsou zapisovány jako desetinná čísla. Tento vnitřní způsob zápisu data Excelu výrazně usnadňuje práci s daty. je číslo, proto 40000-39965=35).
| A | B | C | D | |
| 1 | 1.6.2009 | 6.7.2009 | 35 | =DATEDIF(A1;B1;"D") |
| 2 | 1.6.2009 | 6.7.2009 | 35 | =B2-A2 |
Mezi Dnem dětí a výročím upálení Jana Husa je kalendářních 35 dní.
2) Mnohem užitečnější je funkce DATEDIF v případě, že potřebujeme pracovat s rozdílem mezi dvěma daty v měsících nebo letech.
| A | B | C | D | |
| 1 | 1.7.2009 | 6.7.2009 | 0 | =DATEDIF(A1;B1;"M") |
| 2 | 1.7.2008 | 6.7.2009 | 12 | =DATEDIF(A2;B2;"M") |
| 3 | 1.7.2009 | 31.8.2009 | 1 | =DATEDIF(A3;B3;"M") |
| 4 | 1.1.2009 | 31.12.2009 | 11 | =DATEDIF(A4;B4;"M") |
Excel počítá při použití argumentu "M" rozdíl mezi daty v celých kalendářních měsících. Rozdíl mezi prvním a posledním dnem ve stejném měsíci je tedy 0 měsíců.
3) Obdobně lze pracovat s rozdílem v letech.
| A | B | C | D | |
| 1 | 1.7.2009 | 6.7.2009 | 0 | =DATEDIF(A1;B1;"Y") |
| 2 | 1.7.2008 | 6.7.2009 | 1 | =DATEDIF(A2;B2;"Y") |
| 3 | 1.1.2008 | 31.12.2009 | 1 | =DATEDIF(A3;B3;"Y") |
Pokud jsou data počátku i konce období ve stejném roce nebo mezi nimi není alespoň rok rozdíl, výsledkem bude nula let.
4) Pomocí funkce DATEDIF můžeme také zjistit rozdíly mezi daty, ale bez přihlédnutí k rozdílným rokům či měsícům - jako kdyby nastaly ve stejném roce resp. měsíci.
| A | B | C | D | |
| 1 | 1.6.2008 | 6.7.2009 | 5 | =DATEDIF(A1;B1;"MD") |
| 2 | 1.6.2008 | 6.7.2009 | 35 | =DATEDIF(A2;B2;"YD") |
| 3 | 1.6.2008 | 31.12.2009 | 6 | =DATEDIF(A3;B3;"YM") |
V prvním řádku je ve funkci DATEDIF použit jako argument pro interval "MD", což má za následek výpočet rozdílu dní mezi daty jako kdyby nastaly ve stejném roce a měsíci. Tedy mezi prvním a šestým dnem měsíce je 5 dní.
Ve druhém řádku je použit argument "YD", výpočet ignoruje rok, ale s měsíci počítá. Mezi 1.6. a 6.7. je ve stejném roce 35 dní.
Ve třetím řádku je použit argument "YM", Excel zde počítá rozdíl celých kalendářních měsíců mezi dvěma daty bez ohledu na letopočet. Mezi 1. 6. a 31. 12. je ve stejném roce 6 celých kalendářních měsíců.
Ve sloupci A jsou data vztahující se ke sledovanému období. Chceme zjistit, jak dlouhé je toto období.
| A | B | C | |
| 1 | 17.1.2009 | 197 | =DATEDIF(MIN(A:A);MAX(A:A);"D") |
| 2 | 14.3.2009 | 197 | =MAX(A:A)-MIN(A:A) |
| 3 | 26.1.2009 | 6 | =DATEDIF(MIN(A:A);MAX(A:A);"M") |
| 4 | 6.7.2009 | 15 | =DATEDIF(MIN(A:A);MAX(A:A);"MD") |
| 5 | 25.3.2009 | ||
| 6 | 7.7.2009 | ||
| 7 | 20.1.2009 | ||
| 8 | 22.12.2008 | ||
| 9 | 1.6.2009 | ||
| 10 | 15.5.2009 |
Pokud chceme zjistit délku období, budeme muset počítat s nejstarším a nejnovějším datem. To v Excelu jednoduše zjistíme pomocí funkce MIN(A:A), resp. MAX(A:A) – tento zápis Excelu říká, že má hledat v celém sloupci A, což nemusíme vždy chtít. Pro tento příklad je to však ideální. V prvním řádku je výpočet pomocí funkce DATEDIF s použitím argumentu "D" a ve druhém řádku prosté odečtení obou dat. Délka období je 197 dní.
Ve třetím řádku zjišťujeme délku období v celých kalendářních měsících.
Ve čtvrtém řádku je pomocí argumentu "MD" dopočítán počet dní bez ohledu na měsíce. Spojením údaje ve třetím a čtvrtém řádku zjistíme, že délka sledovaného období je 6 měsíců a 15 dní.
Funkce DATEDIF se často využívá při výpočtu časového období, kde chceme zvlášť oddělit roky, měsíce a dny. Pro výpočet aktuálního věku potřebujeme znát datum narození (sloupec A) a jako konec období zadáme aktuální datum (funkce DNES()).
| A | B | C | |
| 1 | 18.7.1979 | 29Y 11M 18D | =DATEDIF(A1;DNES();"Y")&"Y "&DATEDIF(A1;DNES();"YM")&"M "&DATEDIF(A1;DNES();"MD")&"D" |
| 2 | 6.7.1977 | 32Y 0M 0D | |
| 3 | 4.1.1974 | 35Y 6M 2D |
Pokud se spokojíme s jednoduchým výsledkem s anglickými zkratkami, bude nám stačit spojit jednotlivé části výsledku:
Jednotlivé části výsledku spojujeme jako texty pomocí ampersandu (&). Obdobně bychom mohli použít funkci CONCATENATE. Jak je vidět hned ve druhém řádku, kde je použit stejný vzorec, nevypadá výsledek dobře, jestliže je některá z hodnot nulová. To můžeme jednoduše potlačit pomocí funkce KDYŽ.
| A | B | C | |
| 1 | 18.7.1979 | 29Y 11M 18D |
=KDYŽ(DATEDIF(A1;DNES();"Y")>0;DATEDIF(A1;DNES();"Y")&"Y ";"")& KDYŽ(DATEDIF(A1;DNES();"YM")>0;DATEDIF(A1;DNES();"YM")&"M ";"")& KDYŽ(DATEDIF(A1;DNES();"MD")>0;DATEDIF(A1;DNES();"MD")&"D";"") |
| 2 | 6.7.1977 | 32Y | |
| 3 | 4.1.1974 | 35Y 6M 2D |
Funkci z minulého příkladu doplníme o funkci KDYŽ, kde jako podmínku zadáme DATEDIF(A1;DNES();"Y")>0, což nám vyhodnotí, zda je rok nenulový. V případě, že je výraz pravdivý, vypíše funkce věk v letech, v opačném případě nic. Obdobně v dalších částech vzorce. Ani tento zápis ale není bezchybný, protože pokud rozdíl dat bude přesně v počtu let, za znakem let Y bude zbytečně vypsána mezera.
Rozvinutím předchozího vzorce o další podmínky lze dosáhnout toho, aby Excel skloňoval věk správně česky.
| A | B | C | |
| 1 | 18.7.1979 | 29 let, 11 měsíců a 18 dní |
=KDYŽ(DATEDIF(A1;DNES();"Y")=0;"";KDYŽ(DATEDIF(A1;DNES();"Y")=1;"1 rok"; KDYŽ(DATEDIF(A1;DNES();"Y")<5;DATEDIF(A1;DNES();"Y")&" roky"; DATEDIF(A1;DNES();"Y")&" let")))&KDYŽ(DATEDIF(A1;DNES();"Y")=0;""; KDYŽ(A(DATEDIF(A1;DNES();"YM")>0;DATEDIF(A1;DNES();"MD")>0);", "; KDYŽ(A(DATEDIF(A1;DNES();"YM")>0;DATEDIF(A1;DNES();"MD")=0);" a ";"")))& KDYŽ(DATEDIF(A1;DNES();"YM")=0;"";KDYŽ(DATEDIF(A1;DNES();"YM")=1;"1 měsíc"; KDYŽ(DATEDIF(A1;DNES();"YM")<5;DATEDIF(A1;DNES();"YM")&" měsíce"; DATEDIF(A1;DNES();"YM")&" měsíců")))&KDYŽ(A(NEBO(DATEDIF(A1;DNES();"Y")>0; DATEDIF(A1;DNES();"YM")>0);DATEDIF(A1;DNES();"MD")>0);" a ";"")& KDYŽ(DATEDIF(A1;DNES();"MD")=0;"";KDYŽ(DATEDIF(A1;DNES();"MD")=1;"1 den"; KDYŽ(DATEDIF(A1;DNES();"MD")<5;DATEDIF(A1;DNES();"MD")&" dny";DATEDIF(A1;DNES();"MD")&" dní"))) |
| 2 | 6.7.1977 | 32 let | |
| 3 | 4.1.1974 | 35 let, 6 měsíců a 2 dny |
Podrobný rozbor tohoto poněkud delšího (ale nikoli složitého) vzorce si přiblížíme v následujícím příkladu.
Funkci DATEDIF můžeme dobře využít i v následujícím příkladu, kdy chceme automaticky aktualizovat dobu, která zbývá do určité události. Datum konání akce je zapsán v buňce B3, ke zjištění aktuálního rozdílu můžeme použít funkci DNES.
| A | B | C | |
| 1 | Aktualizace ke dni: 6. červenec 2009 | ||
| 2 | ZOH 2010 Vancouver | ||
| 3 | Termín | 12.2.2010 | |
| 4 | Let | 0 | =DATEDIF(DNES();B3;"Y") |
| 5 | Měsíců | 7 | =DATEDIF(DNES();B3;"M") |
| 6 | Dní | 221 | =DATEDIF(DNES();B3;"D") |
| 7 | Celkem | 0 r, 7 m, 6 d | =DATEDIF(DNES();B3;"Y")&" r, "& DATEDIF(DNES();B3;"YM")&" m, "& DATEDIF(DNES();B3;"MD")&" d" |
Výpočet ve čtvrtém řádku vrací počet celých let, výpočet v pátém řádku pak počet celých měsíců a v pátém řádku počet dní ode dneška do termínu akce. V sedmém řádku je pak vzorec, který spojí počet let, počet měsíců (bez let), počet dnů (bez měsíců a let) s rozlišujícími písmeny r, m a d. Takovýto výsledek je sice správný, ale šel by určitě ještě vylepšit. Pojďme výsledky vylepšit tak, aby byly pro výsledného uživatele lépe čitelné.
1. krok: Ve čtvrtém řádku je zobrazena nula, což je zbytečné. Zobrazení nul můžeme v Excelu potlačit několika způsoby.
=KDYŽ(DATEDIF(DNES();B3;"Y")<>0;DATEDIF(DNES();B3;"Y");"")
Dále by bylo pro lepší přehlednost vhodné upravit formát buněk tak, aby se čísla zobrazovala včetně příslušných jednotek. Tedy například 7 měsíců nebo 221 dní. S ohledem na češtinu budeme potřebovat ošetřit celkem tři intervaly:
Pomocí již zmíněného vlastního formátu čísla můžeme nastavit i takovéto skloňování. V dialogovém okně Formát čísla zvolíme Druh: Vlastní a nastavíme tři intervaly. Například pro buňku B5 bude formát vypadat:
[=1]0" měsíc";[<5]0" měsíce";0" měsíců"
První část zápisu kódu číselného formátu říká, že pokud je hodnota rovna jedné (interval se píše do hranatých závorek, rovnítko je zde nezbytné), pak se má do buňky zapsat výsledek (nula je zástupný symbol pro výsledné číslo) a za něj mezera a slovo měsíc (mezera musí být uvnitř uvozovek - kdybychom ji zapsali před uvozovky, měla by taková mezera zcela jinou funkci – v našem případě ji chceme jako součást textu a proto je součástí uvozovek). Pro takovýto interval bychom také mohli zapsat [=1]"1 měsíc", protože jiné hodnoty logicky nemohou nastat.
Druhá část kódu (jednotlivé části kódu - intervaly - jsou odděleny středníkem) říká, že u hodnot menších než 5 (kromě jedničky, tu už jsme specifikovali a Excel k tomu přihlédne) se má vypsat hodnota a za ní mezera a slovo měsíce.
U ostatních hodnot (zbytek číselné osy si Excel odvodí sám, není třeba zadávat interval) chceme vypsat hodnotu a za ní mezeru a slovo měsíců.
Zcela analogicky můžeme zadat formát i pro dny a roky. Když už jsme u modifikace kódů číselných formátů, můžeme také upravit datum termínu konání akce např. na formát, kdy je den v týdnu a měsíc vypsán slovy.
dddd d. mmmm rrrr
V takovémto formátu je již výsledek mnohem lépe čitelný. Zbývá upravit funkci pro výpis celkové doby v příslušných jednotkách. To bude o trochu složitější, pokud se nespokojíme se zápisem 0 r, 7 m, 6 d, budeme muset použít pro každou část požadovaného výsledku několik vnořených funkcí, které následně pospojujeme, a aby to nebylo zase tak jednoduché, přidáme ještě podmínky pro zápis čárky (jestliže bude ve výsledku nenulový rok, měsíc i den) a spojku a mezi poslední dvě části výsledku.
Při sestrojování složitějších vzorců si můžeme pomoci několika způsoby:
V prvním řádku vzorce řešíme varianty, kdy datum termínu akce je rovno aktuálnímu datu a kdy datum akce je starší než aktuální datum.
Druhý, čtvrtý a poslední řádek výpočtu jsou téměř totožné - liší se jen jednotkami. Jde o tři vnořené funkce KDYŽ, kde postupně ověřujeme čtyři možné intervaly výsledku a přiřazujeme těmto intervalům formát výsledného textu.
V třetím a pátém řádku ověřujeme, zda a případně kolik částí textu bude výsledek obsahovat. Podle toho přidáváme mezi texty čárku nebo písmeno a. Funkce KDYŽ slouží k vnoření podmínek, funkce A a NEBO blíže specifikují podmínku.
Nakonec všechny části textu spojíme pomocí ampersandu nebo funkcí CONCATENATE.
Poslední úpravy, které si v tomto příkladu ukážeme, povedou k zpřehlednění tabulky pro koncového uživatele.
Z druhého sloupce tabulky je zřejmé, že ani Excel není ve svých výpočtech neomylný.
Dejme tomu, že máme ve firmě 100 zaměstnanců a chceme se podívat na to, jak vypadá jejich věková struktura. Ve sloupci A máme výpis zaměstnanců, ve sloupci B jejich data narození. Pro přehled tohoto typu nám bude úplně stačit rozdělit zaměstnance do skupin po deseti letech podle dosaženého věku.
Postup:
