Manažerská informatika
Domů  |  3MA112  |  3MA191  |  3MA381  |  3MA382  |  3MA481   
Osnova cvičení Úvodní informace Podklady ke cvičením Semestrální práce Samostatné aktivity Hodnocení Odkazy

Funkce DATEDIF

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

Syntaxe funkce

=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

K čemu je to dobré

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.

Související funkce

Příklady

Základní operace

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ů.

Zjištění délky období

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í.

Výpočet věku

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.

Odpočítávání času

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.

  1. Pokud potřebujeme skrýt nuly v celém listu, můžeme tak učinit nastavením přes Tlačítko Office - Možnosti aplikace Excel - Upřesnit - Zobrazit možnosti pro tento list, kde vybereme list, na kterém nechceme nulové hodnoty zobrazovat a poté odznačíme položku Zobrazit nulu v buňkách s nulovou hodnotou.
    Skrytí nulových hodnot
  2. Pro skrytí nul pouze pro jednotlivé buňky můžeme využít vlastní formát čísla, kde pro náš účel bude stačit zadat kód číselného formátu např. "0;;" (bez uvozovek).
  3. Další možností je použití funkcí, kde ověříme, zda je výsledná hodnota nenulová a pouze v takovém případě výsledek do buňky zapíšeme. Tedy např. pro naší buňku B4:

=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

Vlastní formát buňky

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:

  1. Postup sestrojení tzv. supervzorce - jednotlivé kroky výpočtu si vypočítáme do samostatných buněk, kde se v druhé a dalších buňkách odkazujeme na předchozí mezivýpočty. Ve chvíli, kdy dosáhneme požadovaného výstupu "složíme" postupně vzorec do jediné buňky s tím, že místo odkazů na předešlé mezivýpočty dosazujeme obsah buněk, na které se výpočet odkazuje. Buňky s mezivýpočty potom můžeme smazat a celý vzorec je v jednom supervzorci. Tento postup může být ale pro někoho nepřehledný a v případě velmi složitého vzorce se v něm může obtížně orientovat.
  2. Jestliže dochází ve vzorci i častému větvení a mezi kroky mezivýpočtu existuje i množství vazeb (jako je to v našem případě nyní), můžeme si takový vzorec nakreslit a pak při konstrukci vzorce postupovat podle nákresu.
Vzorec jako schéma
DATEDIF - vzorec

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.

  1. Na začátek tabulky přidáme nadpis.
  2. Do dalšího řádku dáme informaci o aktuálním datu pomocí funkce DNES. Pro naformátování můžeme použít dříve zmíněného vlastního formátu buňky, kde text dáme do uvozovek "Aktualizace ke dni: "d. mmmm rrrr nebo můžeme použít funkci HODNOTA.NA.TEXT a texty spojit ="Aktualizace ke dni: "&HODNOTA.NA.TEXT(DNES();"d. mmmm rrrr").
  3. Použijeme vhodné obrázky pro jednotlivé akce. Tabulka bude rázem přehlednější.
  4. Pokud máme tabulku s několika termíny, můžeme ještě graficky odlišit poměry mezi délkou zbývajících dní do akce pomocí podmíněného formátování - v tomto případě se hodí např. datové čáry v řádku s termínem akce.
Funkce DATEDIF - termíny akci

Z druhého sloupce tabulky je zřejmé, že ani Excel není ve svých výpočtech neomylný.

Rozdělení zaměstnanců do skupin dle věku

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.

Věk zaměstnanců v kategoriích

Postup:

  1. Do sloupce C dopočítáme dosažený věk v letech - pro buňku C2 bude vzorec vypadat jako =DATEDIF(B2;DNES();"Y").
  2. Připravíme si tabulku o dvou sloupcích. Do prvního vyplníme např. pomocí vyplnění řady hodnoty 20-70 po deseti letech. Počet zaměstnanců v jednotlivých skupinách můžeme dopočítat několika způsoby. Nabízí se např. funkce ČETNOSTI nebo COUNTIF.
    Rozdělení do skupin pomocí funkce ČETNOSTI Rozdělení do skupin pomocí funkce COUNTIF
  3. Nyní již zbývá jen vytvořit z tabulky graf - to můžeme např. pomocí klávesové zkratky ALT+F1.

Použité zdroje a zdroje dalších informací

Nahoru

© 2007–2013 Miroslav Lorenc

Převzetí, publikování, kopírování, či jiné užití než pro osobní potřebu je možné pouze s písemným svolením autora.