# Pánem času v Excelu

Datum a čas

autor: Ing. Petr Pecháček
http://officir.web3.cz

Sešit Excelu s uvedenými příklady (komprese ZIP)

## Na úvod

MS Excel standardně pracuje v systému 1900. To znamená, že najzažším datumem, se kterým umí počítat, je 1.1.1900. (Existují nadstavby pro počítání s dřívějšími datumy.) Každé datum má své pořadové číslo. Datum 1. ledna 1900 má pořadové číslo jedna a například 2. květnu 2004 odpovídá číslo 38109. Z toho vyplývá, že prostým odečtením dvou datumů získáme rozdíl ve dnech. Excel pochopitelně uvažuje i přestupné roky, tedy 29. února. Musím ovšem uvést, že na rozdíl od roku 2000 rok 1900 nebyl přestupný, jak nám předkládá Excel. Paradoxní je to, že vývojáři úmyslně tuto chybu zakomponovali z důvodu kompatibility též chyby v Lotusu 1-2-3. A nakonec tohoto odstavce jedna poznámka. Ačkoliv se často v hovorové řeči setkáváme s pojmem data ve smyslu datumy, pokusím se vždy používat slovo datumy.

## Zadáváme datum a čas

Excel za určitých okolností automaticky formátuje vložená data na typ datum nebo čas. Někdy je to výhodné, jindy ne. Obzvláště problematické typy hodnot jsou v obrázku vyznačeny červeně. Tečka, dvojtečka, lomítko, pomlčka či znaménko mínus předurčují vkládaná data k autoformátování. Zadáváme-li rok pouze dvojčíslím, řídí se Excel uvedeným nastavením. Dle mého názoru není třeba jej měnit.

## Formátujeme datum

Jak jste již asi postřehli, formát buňky pro datum určují tři základní písmenka (d ... den, m ... měsíc, y ... rok), kdy jejich počet vedle sebe určuje typ zobrazení dané časové jednotky. Ta základní zobrazení jsou uvedena, ostatní vyzkoušejte (menu Formát / Buňky / karta Číslo). Nejprve klepněte na položku datum, vyberte typ, přepněte se na typ vlastní, a podle předlohy zaměňujte sled písmenek v původním zápisu. Význam formátování je jak vizuální, tak účelový. Už teď například umíte zjistit, který den jste se vlastně narodili a co je podstatné, bez použití funkce!

## Základní funkce

Aktuální datum či čas se vkládá pomocí funkcí DNES a NYNÍ, které se obnovují při přepočítávání listu nebo například otevírání sešitu. (Jednorázově lze kombinací Ctrl+; vložit do buňky aktuální datum a kombinací Ctrl+Shift+: aktuální čas.) Funkce DATUM si ponechává neevropskou posloupnost rok-měsíc-den. Povšimněte si chování této funkce při "přetečení" měsíců přes dvanáctku. Cifry pořadového čísla za desetinnou čárkou pak vyjadřují části dne, tj. například 0,5 vyjadřuje polovinu dne, jinak řečeno 12 hodin.

## Počítáme s datumem

Obrázek vlevo je vystřižen z kalendáře systému Windows a slouží k ověření vzorců uvedených a popsaných níže. (I takový nekonečný kalendář lze vytvořit v Excelu.) A nyní už se věnujme příkladům.

Řádek 4 a 9:
  Využíváno je zde v podstatě jen formátování čísla buňky (Formát buňky/karta Číslo/Druh: Vlastní, kdy vycházíme z formátů pro datum).
Řádek 5:
  Funkce DENTÝDNE vrací pořadové číslo dne týdne, jenž je získáno z číselného vyjádření datumu. Dvojka před pravou závorkou říká Excelu, že týden má začínat pondělím s pořadovým číslem jedna.
Řádek 6 a 10:
  Zdánlivě jde o zbytečné použití funkce HODNOTA.NA.TEXT v porovnání s řádky 4 a 9. Ovšem jedna podstatná obměna tu je. Výsledkem je totiž text (viz automatické zarovnání v buňce). Pokud bychom chtěli například kopírovat řetězec 25.2.2004 z buňky C1 na jiné místo Excelu, dostaneme vždy pořadové číslo (zde 38042), nikoliv text a to i v případě volby Úpravy/Vložit jinak.../Hodnoty, kdežto v případě kopie z buňky, u níž byl aplikován vzorec HODNOTA.NA.TEXT, získáme skutečně text. (Ve Visual Basicu for Application je vše v pořádku, neboť vlastnost Range("C1").Text vrátí očekávaný řetězec "25.2.2004".) Máme-li zájem o kopii řetězce představujícho datum do textového editoru či jiné externí aplikace přes schránku, nemusíme se tímto zabývat.
Řádek 7:
  První skutečně užitečný řádek, kdy s pomocí funkce WORKDAY najdeme datum posunuté o daný počet pracovních dní dopředu či zpět. Funkce vyžaduje instalaci Analytických nástrojů (viz Nástroje/Doplňky) a umí vyloučit i svátky zapsané do oblasti listu.
Řádek 8:
  Funkce WEEKNUM vrací pořadové číslo týdne roku odpovídající vstupnímu datumu. Funkce vyžaduje instalaci Analytických nástrojů (viz Nástroje/Doplňky). Zde byl vynechán úmyslně druhý parametr, jehož význam je stejný jako u funkce DENTÝDNE (viz nápověda). Přečtěte si (v angličtině) úvahu na stránkách Chipa Pearsona na dané téma. Autor se rovněž vyjadřuje k normovanému ISO výpočtu týdne roku.
Řádek 11:
  Zaokrouhlovací funkce ROUNDUP zde hraje úlohu při výpočtu čtvrtletí. Myslím, že není třeba vysvětlovat princip.
Řádek 12 a 13:
  Tyto řádky kromě složitějšího algoritmu obsahují i vlastní funkci VBA nazvanou CISLODNE, jež je vlastně doplňkovou funkcí k DENTÝDNE. Narozdíl od ní příjímá jako vstupní parametr slovně zadaný den týdne, nikoliv datum. Jste-li programátory, její kód si můžete prohlédnout v editoru VBA (stiskněte Alt+F11 v prostředí Excelu) po spuštění sešitu s příklady (dostupný ke stažení z této stránky).
Řádky 14 až 17:
  Fakt, že Excel pracuje s datumy jako pořadovými čísly je zde uplatněna k přičítání a odčítání dní či týdnů.
Řádek 18 a 19:
  Pro zjištění datumu posunutého od daného datumu o nějaký ten měsíc nabízí Excel funkci EDATE určenou původně pro hospodářské výpočty. Funkce vyžaduje instalaci Analytických nástrojů (viz Nástroje/Doplňky).
Řádek 20 až 23:
  Může se stát, že potřebujeme ohraničit měsíc, ve kterém se datum nachází. Vystačíme si s běžnými funkcemi. Funkce EOMONTH je zde uvedena jen jako alternativní možnost a vyžaduje instalaci Analytických nástrojů (viz Nástroje/Doplňky).

## Počítáme s časem

Příklady ukazují, jak se vypořádat s nejčastějšími problémy: převod jednotky času na jiný zápis, sčítání hodin přesahujících jeden den a rozdíly časů překračujících půlnoc.

## Mezi dvěma datumy...

Jak již bylo řečeno, prostým odečtem dvou datumů získáme rozdíl ve dnech (buňku je nutno přeformátovat na obecný typ). Ale jak je tomu při výpočtu měsíců a let? Roky jsou přestupné, měsíce mají proměnlivý počet dnů... nebudu Vás nyní trápit teorií, jen zdůrazním, že rozdíl počítaných jednotek je vždy závislý na jednotkách o řád menších (roky na měsících, měsíce na dnech atd.). Jak tedy z toho ven? Jak se dopočítat například stáří patnácti let, tři měsíců a dvou dnů? Smutným faktem je neznalost málo prezentované a dokumentované funkce DATEDIF (která naštěstí díky zachování kompatibility s Lotusem 1-2-3 v Excelu zůstává - v nápovědě ji hledejte z karty Rejstřík). No a pokud vám to nedá a vytvoříte svou vlastní funkci, můžete ji porovnat s ostatními (v přiloženém sešitu viz kód VBA, stránky Chipa Pearsona)

## Generátor časových řad

Generátor řad, ať už datumový či časový (v sešitu ke stažení jsou uvedeny oba) slouží k pohodlnému vytváření posloupností s přírůstkem jedné jednotky, či více časových jednotek současně. Volitelné je jak počáteční datum, tak jednotka kroku. Po vyplnění požadovaných hodnot pouhým tažením buňky (za úchytný bod) v řádku 12 a patřičném sloupci dojde k automatickému vygenerování řady, jíž následně kopírováním přesunete do svého sešitu.
Již dříve byla zmíněna možnost zahrnutí svátků do funkcí NETWORKDAYS a WORKDAY. Zde je toho využito k vytvoření generátoru pracovních dní. Samotné datumy svátků jsou vytvářeny automaticky v závislosti na zadaném roku v buňce U3. V korespondenci s tím je nutné volit i počáteční datum řady v buňce U23. Tažením U24 stejně jako v předešlém případě získáme pokračování řady.

## Úlohy k zamyšlení

- Zkuste s pomocí Excelu popřát svým známým při příležitosti 10000 dnů, co jsou na světe!
- Víte, kdo jsou (byli) prvotřídní experti pro výpočty s časem? Odpověď zní: Vojáci základní služby. Jejich nástrojem byla papírová tabulka o zhruba 365 dnech, tzv. "hektarka". Dokážete vytvořit elektronickou podobu s údaji o dnech uplynulých, zbývajících? Tabulka by měla počítat s dovolenou, svátky, víkendovými dny...
- Napadá vás, jak ověřit, zda-li je rok přestupný?

Přestupný rok Ověříme existenci 29. února roku umístěného například v buňce A1. Výsledkem je PRAVDA v případě, že se jedná o rok přestupný, jinak dostaneme výsledek NEPRAVDA.
    =KDYŽ(MĚSÍC(DATUM(A1;2;29))=2;"PRAVDA";"NEPRAVDA")
A ještě jednou... Rok je přestupný, jestliže je beze zbytku dělitelný čtyřmi a nedělitelný stem nebo je dělitelný stem a současně čtyřmi sty. Ověříme takto hodnotu umístěnou v buňce A1. Výsledkem je PRAVDA v případě, že se jedná o rok přestupný, jinak dostaneme výsledek NEPRAVDA.

    =NEBO(A(MOD(A1;4)=0;MOD(A1;100)>0);A(MOD(A1;100)=0;MOD(A1;400)=0))

Připomínám, že rok 1900 přestupný nebyl, i když Excel tvrdí něco jiného.
Velikonoční pondělí Tento svátek je závislý na úplňku měsíce. O tom, jak autor došel k následujícímu vzorci, je známo jen velmi málo. Podstatné je, že je funkční. V následujícím příkladu zjišťujeme datum pro rok umístěný v buňce A1.
    =KČ(("4/"&A1)/7+MOD(19*MOD(A1;19)-7;30)*14%;)*7-5

Sešit Excelu s uvedenými příklady (komprese ZIP)

pro časopis