Tipy a triky v Exceli 5 – funkcie s dátumom

V seriáli Tipy a triky v Exceli prinášame odpovede na otázky účastníkov súvisiace s Excelom, ktoré nie sú bežne v osnove našich kurzov. Vytvorili sme tak mozaiku odpovedí, ktoré by mohli pomôcť pri práci s Excelom i vám…
Toto je piata časť, v ktorej sa zaoberáme najmä funkciami času, ako počítať v Exceli vek alebo dĺžku pracovného pomeru a pod.

Stretli ste sa už s potrebou vypočítať vek človeka s presnosťou na roky, mesiace alebo až s presnosťou na deň? Alebo s úlohou ako zistiť, či má rodič priniesť potvrdenie o návšteve školy, pretože jeho dieťa prekročilo vek 18 rokov? Ako vypočítať dobu trvania pracovného pomeru v podniku? Koľko dní bude trvať cesta zásielky loďou z prístavu v Ázii do Európy?

V Exceli to vieme spraviť jednoducho i zložito, veľmi presne aj približne… rýchlo i pomaly 🙂

Dátumové funkcie v Exceli

Excel má medzi svojimi takmer päťsto funkciami viaceré pomáhajúce pri počítaní s dátumami a časom.
Na obrázku vidíte v hornej časti prehľad s popisom tých základných – DAY, MONTH, YEAR, WEEKNUM a ISOWEEKNUM.
Prvé tri funkcie vrátia z dátumu hodnotu deň, mesiac alebo rok. Výsledkom WEEKNUM je poradové číslo týždňa dátumu v odkazovanej bunke. ISOWEEKNUM vráti tiež poradové číslo týždňa dátumu v odkazovanej bunke, na rozdiel od WEEKNUM je však výsledok číslo týždňa podľa medzinárodného štandardu. V praxi sa však viac využívajú ďalšie dve funkcie.

Mimochodom – vedeli ste, že čísla týždňov sa vo svete líšia?
Existujú krajiny, ktoré považujú za prvý týždeň v roku ten, v ktorom je 1. január. Súčasne existujú krajiny považujúce za prvý týždeň roka ten, v ktorom pripadne 1. január pondelok, utorok, streda alebo štvrtok. Alebo keď u nich oslavujú nový rok niekedy v januári…  Tak ktorý je prvý týždeň roka?
Medzinárodná štandardizačná organizácia zaviedla koncom 80-tych rokov definíciu ISO 8601. Prvý týždeň v ISO roku je ten, v ktorom je prvý štvrtok v gregoriánskom roku, teda ten týždeň, ktorý obsahuje dátum 4.januára.

Načo je to dobré?
Napríklad… exportér oznámi zahraničnému obchodnému partnerovi, že tovar bude pripravený na expedíciu od utorka v 23. týždni. Ten objedná na prepravu kamión, loď, lietadlo na prevoz tovaru do svojho skladu. A… nič. Kamión čaká, zásielku nevedia v sklade nájsť. Telefonáty, nadávky, nervozita. Po hodine pátrania sa zistí, že informácia je správna, avšak nový pracovník použil miestne označenie týždňa. Nenapadlo mu, že v zahraničí môže byť týždeň označený inak (vlastná skúsenosť autora s exportérom). Môže.

Ako vypočítať vek v Exceli

Pozrime sa na to ako počítať s dátumom v Exceli na konkrétnych príkladoch.
Zaujíma nás – väčšinou pre potreby rôznych administratívnych úkonov v práci – koľko rokov má človek (pracovník, dieťa,…) k aktuálnemu „dnešnému“ dňu. Zaujíma nás ako vypočítať v Exceli vek? Počet odpracovaných rokov?
Nie je to ťažké, ak poznáme správne funkcie, prípadne vieme ako Excel pracuje s dátumami.

Predpokladajme, že osoby, ktorým ideme zisťovať vek, máme usporiadané v tabuľke tak ako na obrázku.

1. spôsob

Excel nám umožňuje jednoduchým rozdielom dvoch dátumov zistiť koľko dní uplynulo medzi dvoma dátumami. Preto by sme mohli do susedného stĺpca vedľa stĺpca dátumu narodenia (označený teraz nesprávne ako Vek) napísať aktuálny dnešný dátum. A do nasledujúceho stĺpca napíšeme vzorec, ktorým vypočítame rozdiel dvoch buniek s dátumami v riadku vedľa. Takto to robia začiatočníci, úplne na začiatku počítania s Excelom.
Výsledok je správny, ale…

(Ak sa v stĺpci objaví namiesto počtu dní nič nehovoriaci dátum, nezúfajte. Problém je len vo formáte buniek. Nastavte pre výber v hornom okienku na karte Domov na paneli Číslo formát Všeobecné.)

Získaný výsledok má jednu nevýhodu – bude platiť iba k dátumu 7.2.2020. Trošku pokročilejší „excelák“ bude preto uvažovať nad vzorcom s funkciou, ktorá by výpočet uľahčila.

Aby sme neboli viazaní len na jediný dátum „dneška“, ktorý sme uviedli pri predchádzajúcom spôsobe výpočtu do stĺpca D, použijeme pri výpočte vo vzorci ďalšiu funkciu. Dátumová funkcia TODAY v Exceli používa vždy aktuálny dátum z kalendára počítača. Teda aktuálny dátum nemusíme vôbec písať do tabuľky. Výpočet prebehne vždy, keď súbor otvoríme, k novému aktuálnemu „dnešnému“ dátumu. Dobré, však?

Funkcia vyzerá pri zápise trošku čudne, pretože je bezargumentová – nemá v zátvorke žiadny argument. Vzorec s ňou vyzerá v bunke takto:

=TODAY()

Dovnútra funkcie nepíšeme nič, ani medzeru. Iba ľavá a pravá zátvorka.
TODAY nám zobrazí v bunke výsledok v podobe aktuálneho dátumu.

Pri našom príklade funkciu oceníme, pretože po jej použití vyzerá riešenie nasledovne.
Namiesto dátumu do prvej bunky stĺpca D (D7) vpíšeme rozdielový vzorec:

=TODAY()-C7

a skopírujeme ho do buniek nižšie dvojklikom na štvorčekový úchyt (rukoväť) v rohu bunky.
(Podobne ako v predchádzajúcom postupe, ak sa v stĺpci objaví namiesto počtu dní nič nehovoriaci dátum, problém je len vo formáte buniek. Nastavte pre označený výber v hornom okienku na karte Domov namiesto formátu Dátum formát Všeobecné.)

Takto sme dostali počet dní života pracovníka. Ak chceme počet rokov, výsledok musíme ešte premeniť na roky. Ako?
Keďže každý štvrtý rok je priestupný, vydelíme získaný počet dní života číslom 365,25. Dostaneme pomerne presný vek všetkých pracovníkov.
Celý vzorec v prvej bunke potom vyzerá nasledovne:

=(TODAY()-C7)/365,25

Dvojklikom na úchyt ho skopírujeme do celého stĺpca.
Rozdiel dvoch dátumov v rokoch sme takto zistili cez vzorec s funkciou TODAY.
Ním vypočítame rozdiel dvoch buniek s dátumami.

Výsledok môžeme zobraziť len na celé roky pomocou funkcie INT (integer), ktorá zabezpečí zobrazenie len celočíselnej časti. Vyzeralo by to takto:

=INT((TODAY()-C7)/365,25)

Poznámka:
V prípade, že potrebujeme počítať vek ku konkrétnemu dňu, napríklad k 31.12.2019, namiesto TODAY napíšeme do vzorca konkrétny dátum v tvare DATE(2019;12;31).

2. spôsob

Použijeme na výpočet inú dátumovú funkciu Excelu – YEARFRAC. Funkcia YEARFRAC má dva povinné argumenty (počiatočný a koncový dátum) a voliteľný argument podľa relevantnej finančnej operácie (bežne ho nepoužívame).

Výpočet spravíme vo vedľajšom stĺpci, aby sme mohli porovnať výsledky. V prvej bunke bude vyzerať nasledovne:

= YEARFRAC(c7;TODAY())

Dvojklikom na štvorčekový úchyt ho skopírujeme do zvyšku stĺpca.
Rozdiel medzi dvoma dátumami sme zistili pomocou funkcie YEARFRAC, ktorou počítame rozdiel dvoch buniek s dátumami.

Pri porovnaní oboch stĺpcov Vek vidíme rozdiel. Vek počítaný rozdielom a delením 365,25 nie je celkom presný. Výsledok získaný funkciou YEARFRAC je pri korektne zadaných vstupných hodnotách presný (vo výnimočných prípadoch počítania bankových rokov 30/360 s počiatočným dátumom 29.2. môže vrátiť nesprávne výsledky).
Ak nás zaujíma len počet rokov, jeden i druhý uvedený výpočet postačuje.

Ak potrebujeme počítať rozdiel dátumov ku konkrétnemu dňu, napríklad k 31.12.2019, aj pri použití funkcie YEARFRAC namiesto TODAY() napíšeme do jej vnútra konkrétny dátum v tvare DATE(2019;12;31).

(V poslednom riadku vidno v bunkách použité vzorce v označenej bunke stĺpca nad nimi.)

Ako vypočítať dobu trvania pracovného pomeru

Dobu trvania pracovného pomeru počítame v Exceli podobne ako v predchádzajúcom prípade.
Pozrite sa na tabuľku.
Namiesto dátumu narodenia do nej vpíšeme alebo skopírujeme z inej tabuľky dátumy nástupu pracovníkov do zamestnania. Postup i logika pri vytváraní vzorca je podobná.

Výpočet spravíme pomocou funkcie YEARFRAC v stĺpci G.
V prvej bunke bude vyzerať nasledovne:

=YEARFRAC(F7;TODAY())

Dvojklikom na štvorčekový úchyt v rohu bunky vzorec skopírujeme do zvyšku stĺpca.

Ak potrebujeme len celé roky, alebo sa nám nepáči počet desatinných miest, môžeme ho upraviť pomocou nástrojov so šípkami na karte Domov na paneli Číslo (Formát čísla). Pridávajú a uberajú počet zobrazených desatinných miest. Alebo môžeme priamo vo vzorci použiť funkciu INT spomínanú vyššie takto:

=INT(YEARFRAC(F7;TODAY()))

V prípade, že potrebujeme počítať počet odpracovaných rokov ku konkrétnemu dňu, napríklad k 31.12.2019, namiesto TODAY() napíšeme do vnútra funkcie konkrétny dátum v tvare DATE(2019;12;31), teda vzorec bude vyzerať takto:

=INT(YEARFRAC(F7;DATE(2019;12;31)))

Samozrejme, že dobu trvania pracovného pomeru vieme vypočítať aj prvým spôsobom popísaným vyššie – odčítaním dvoch dátumov a vydelením rozdielu 365,25. Výsledok bude o chĺpok menej presný ako funkciou YEARFRAC.

A je to!

Pre fajnšmekrov v Exceli

Výpočet veku či obdobia môže mať veľa podôb v závislosti na požiadavkách zobrazenia výsledku.
Vzorec na jednoduché zobrazenie počtu rokov (prežitých, odpracovaných,…) k dnešku môže mať takýto tvar:

=DATEDIF(C3;TODAY();“y“)

pričom v bunke C3 bude dátum, od ktorého počítame roky, t.j. dátum narodenia, dátum nástupu do zamestnania,…
Vzorec na presnejšie zobrazenie výpočtu rokov, mesiacov i dní môže vyzerať napríklad aj takto:

=DATEDIF(C3;TODAY();“y“)&“ r. „&DATEDIF(C3;TODAY();“ym“)&“ m. „&DATEDIF(C3;TODAY();“md“)&“ d.“

Viacero spôsobov výpočtu vrátane problémov pri použití skloňovaných slovenských slov rok, roky, rokov,… aj s anglickou verziou vidno na obrázku nižšie. Ak chce niekto tieto vzorce otestovať alebo využiť v praxi, kliknutím na tlačidlo na konci článku si môžete stiahnuť súbor s excelovským hárkom do svojho počítača.

Pri výpočte v tomto vzorci som použil menej známu dátumovú funkciu Excelu DATEIF.
Jej podrobnejší popis nájdete od roku 2019 aj v pomocníkovi k Excelu (kláves F1).

Záver

Ako vidno z poslednej tabuľky, pri počítaní s dátumom existuje viacero riešení.
Pre praktické použitie odporúčam – vyberte si to, ktorému najlepšie rozumiete. Aby ste ho vedeli kedykoľvek bez problémov interpretovať, prípadne upraviť podľa potreby.

Tieto isté princípy a vzorce môžeme použiť v praxi napríklad aj pri zisťovaní, či má rodič zamestnávateľovi priniesť potvrdenie o návšteve školy (vo vzorci použijeme pri odrátaní dátum narodenia dieťaťa a TODAY alebo namiesto TODAY konkrétny dátum pomocou DATE, ku ktorému povinnosť zisťujeme) a pri množstve iných praktických úloh. Excel nám uľahčí pomocou svojich funkcií život 🙂

Chcete vedieť o práci s Excelom a časovými funkciami viac? Príďte na kurz Excelu, dozviete sa a na príkladoch vyskúšate. Už po dvoch dňoch strávených na kurze Excel I, či Excel II budete vedieť o práci s týmto tabuľkovým procesorom viac, osvojíte si klávesové skratky, spôsoby rozlišovania typov tabuliek a kedy ktorú použiť, dozviete sa ako vytvárať vzorce, používať funkcie,… je toho veľa.
Počítať s dátumom budete vedieť už po Excel II, časové funkcie preberáme podrobnejšie na Excel III 🙂