Sprievodca výpočtom veku a prácou s dátumami v Exceli: Od narodenia po pracovné dni

Microsoft Excel je nástroj, ktorý nám pomáha rôznymi spôsobmi nielen s číslami, ale aj s dátumami a časom. Práca s dátumami je veľmi užitočná pri rôznych administratívnych úkonoch v práci, pri plánovaní, alebo napríklad pri sledovaní osobných údajov. Či už potrebujete vypočítať vek človeka s presnosťou na roky, mesiace alebo až s presnosťou na deň, alebo zistiť dobu trvania pracovného pomeru v podniku, Excel ponúka funkcie a nástroje, ktoré tieto úlohy zvládnu s prehľadom. V tomto článku Vám priblížime prácu s dátumovými funkciami a ukážeme si, ako efektívne využívať Excel na správu a analýzu dátumových informácií, vrátane výpočtu dní od narodenia.

Základy práce s dátumami v Exceli: Poradové čísla a formáty

Predtým, než sa ponoríme do špecifických funkcií, je dôležité pochopiť, ako Excel spracováva dátumy. V Microsoft Exceli pre Windows sa na základe predvoleného nastavenia používa dátumový systém 1900, čo znamená, že prvý dátum je 1. január 1900. Každý dátum je v skutočnosti interné číslo, takzvané poradové číslo, ktoré predstavuje počet dní, ktoré uplynuli od tohto základného dátumu. Napríklad, dátum 2. január 1900 má poradové číslo 2. Tento systém umožňuje Excelu vykonávať matematické operácie s dátumami rovnako ako s inými číslami.

Je kľúčové si uvedomiť, že dátumy by sa mali zadávať pomocou funkcie DATE alebo ako výsledok iných vzorcov alebo funkcií. Ak budete mať dátumy ako texty, nebudete s nimi vedieť pracovať ako s dátumom. Excel tieto hodnoty nebude považovať za dátumy a nebude schopný s nimi vykonávať dátumové výpočty. Preto je nevyhnutné skontrolovať, či je bunka s dátumom formátovaná ako číslo alebo Všeobecné. Ak sa v tejto bunke nezobrazuje číslo, skontrolujte, či je formátované ako číslo alebo všeobecné.

Kľúčové dátumové funkcie pre základnú manipuláciu

Excel ponúka širokú škálu dátumových funkcií, ktoré sú veľmi užitočné pri práci s dátumami a časom, či už priamo v tabuľkách alebo vo VBA či iných programovacích jazykoch. Tieto funkcie nám umožňujú rozkladať dátumy na ich základné zložky, vytvárať dátumy z jednotlivých častí a pracovať s aktuálnym dátumom.

Funkcie DAY, MONTH, YEAR: Extrahovanie zložiek dátumu

Tieto funkcie sa používajú pri zistení dňa (DAY), mesiaca (MONTH) alebo roku (YEAR) z určitých dátumov, ktoré sú vyjadrené poradovým číslom.

• DAY (DEŇ): Vráti deň z dátumu, ktorý je vyjadrený poradovým číslom. Poradové_číslo je povinný argument a predstavuje dátum, ktorého deň sa pokúšate vyhľadať.
• MONTH (MESIAC): Vráti mesiac z dátumu, ktorý je vyjadrený poradovým číslom. Poradové_číslo je povinný argument a predstavuje dátum, ktorého mesiac sa pokúšate nájsť.
• YEAR (ROK): Vráti rok zodpovedajúci dátumu, ktorý je vyjadrený poradovým číslom. Poradové_číslo je povinný argument a predstavuje dátum, ktorého rok chcete vyhľadať.

Funkcia DATE (DÁTUM): Vytvorenie dátumu z jednotlivých zložiek

Funkcia DATE (v angličtine DATE) je nevyhnutná na vytvorenie platného dátumu v Exceli. Vráti poradové číslo, ktoré predstavuje konkrétny dátum zo zadaných hodnôt (deň, mesiac, rok).

• Rok: Povinný argument. Hodnota argumentu Rok sa môže skladať z jednej až štyroch číslic. Excel interpretuje argument Year podľa systému dátumov, ktorý Váš počítač používa.
• Mesiac: Povinný argument.
• Deň: Povinný argument. Číslo predstavuje deň v mesiaci od 1 do 31.Funkcia DATE (DÁTUM) vráti dátum (poradové číslo). Dátumy by sa mali zadávať pomocou funkcie DATE alebo ako výsledok iných vzorcov alebo funkcií.

Funkcia TODAY (DNES): Použitie aktuálneho dátumu

Pri práci s dátumami často potrebujeme odkazovať na aktuálny „dnešný“ dátum. Dátumová funkcia TODAY v Exceli používa vždy aktuálny dátum z kalendára počítača. To znamená, že 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. Funkcia vyzerá pri zápise trošku čudne, pretože je bezargumentová - nemá v zátvorke žiadny argument. Dovnútra funkcie nepíšeme nič, ani medzeru. Ak sa v stĺpci objaví namiesto počtu dní nič nehovoriaci dátum, problém je len vo formáte buniek.

Výpočet veku a dní od narodenia v Exceli

Jednou z najčastejších úloh s dátumami je vypočítať vek osoby alebo dobu trvania medzi dvoma dátumami. Excel nám umožňuje jednoduchým rozdielom dvoch dátumov zistiť, koľko dní uplynulo medzi dvoma dátumami. Existuje viacero spôsobov, ako v Exceli vypočítať vek osoby.

Jednoduchý rozdiel dátumov: Zistenie počtu dní

Najjednoduchší spôsob, ako zistiť počet dní, ktoré uplynuli medzi dvoma dátumami, je odčítať ich. Napríklad, ak máme dátum narodenia v bunke A1 a aktuálny dátum v bunke B1, vzorec =B1-A1 nám vráti počet dní. Ak sa v stĺpci objaví namiesto počtu dní nič nehovoriaci dátum, nezúfajte. Problém je len vo formáte buniek. Získaný výsledok má jednu nevýhodu - bude platiť iba k dátumu, ku ktorému bol výpočet vykonaný. Aby sme neboli viazaní len na jediný dátum „dneška“, ktorý by sme uviedli pri predchádzajúcom spôsobe výpočtu do stĺpca, použijeme pri výpočte vo vzorci funkciu TODAY(). Takto sme dostali počet dní života pracovníka, ktorý sa automaticky aktualizuje pri každom otvorení súboru.

Výpočet rokov s ohľadom na priestupné roky

Ak chceme namiesto počtu dní života získať počet rokov, výsledok musíme ešte premeniť na roky. Keďže každý štvrtý rok je priestupný, vydelíme získaný počet dní života číslom 365,25. Výsledok môžeme zobraziť len na celé roky pomocou funkcie INT (integer), ktorá zabezpečí zobrazenie len celočíselnej časti.

Presnejší výpočet veku pomocou funkcie YEARFRAC

Na výpočet veku alebo doby trvania v rokoch je možné použiť aj inú dátumovú funkciu Excelu - YEARFRAC (ROČNÉ.ČASŤ). Táto funkcia vypočíta dobu medzi dvoma dátumami vo forme desatinného čísla (napr. celý rok=1, pol roka=0,5).Syntax funkcie je: =YEARFRAC(začiatok; koniec; [základňa])

• začiatok: Počiatočný dátum.
• koniec: Konečný dátum.
• [základňa]: Spôsob výpočtu (predvolene = 0). Počíta sa ako zlomek, v ktorom sa môže líšiť postup výpočtu ako čitateľa (počet dní medzi dátami), tak menovateľa (počet dní celého roka).
  • 0 = US NASD 30/360
  • 1 = Skutočný/Skutočný
  • 2 = Skutočný/360
  • 3 = Skutočný/365
  • 4 = Európske 30/360kde: Skutočný v čitateli = počet dní medzi dátami vypočítaný funkciou DAYS. Skutočný v menovateli = priemer počtu dní všetkých rokov, cez ktoré ide interval (vypočítaný funkciou DAYS medzi 1.1. a 31.12.).

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 dôjsť k drobným odchýlkam). 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), ktoré pridávajú a uberajú počet zobrazených desatinných miest. Pri porovnaní stĺpcov vypočítaných oboma spôsobmi (rozdielom a delením 365,25 a funkciou YEARFRAC) vidieť, že vek počítaný rozdielom a delením 365,25 nie je celkom presný.

Funkcia DATEDIF (SKRYTÁ FUNKCIA): Presný výpočet intervalov

Pre najpresnejší výpočet veku s rozčlenením na roky, mesiace a dni je v Exceli k dispozícii skrytá funkcia DATEDIF. Táto funkcia vypočíta počet dní, mesiacov alebo rokov medzi dvoma zadanými dátumami. Je dôležité vedieť, že vo starších verziách mohla funkcia dávať nepresné výsledky (zvlášť u parametra MD) podľa priestupnosti roku. Funkcia je skrytá, nenapovedá sa v našeptávači, ale je plne funkčná.Syntax je: =DATEDIF(date1; date2; interval)

• date1: Počiatočný dátum.
• date2: Konečný dátum.
• interval: Typ obdobia, ktorého počet sa má počítať, zadáva sa ako textový reťazec v úvodzovkách:
  • "Y" = počet celých rokov.
  • "M" = počet celých mesiacov.
  • "D" = počet dní.
  • "MD" = počet dní, pričom sa neprihliada k mesiacom a rokom.
  • "YM" = počet celých mesiacov, pričom sa neprihliada ku dňom a rokom.
  • "YD" = počet dní, pričom sa neprihliada k rokom, ale k mesiacom áno.

Príklady použitia DATEDIF:

• =DATEDIF(A1; A2; "M"): Počet celých mesiacov, ktoré uplynuli medzi dátumom v bunke A1 a dátumom v bunke A2.
• =DATEDIF(A1; A2; "YM"): Počet celých mesiacov, ktoré uplynuli medzi dátumom v bunke A1 a dátumom v bunke A2 s ignorovaním roku.

Túto funkciu často využívame, keď nás zaujíma - 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. Príkladom môže byť výpočet: =DATEDIF(C3;TODAY();"y")&" r. "&DATEDIF(C3;TODAY();"ym")&" m. ".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. Pre praktické použitie sa odporúča vybrať si to, ktorému najlepšie rozumiete.

Pokročilé dátumové funkcie a ich praktické využitie

Okrem základných výpočtov veku a intervalov ponúka Excel aj pokročilé funkcie, ktoré sú neoceniteľné pri komplexnejšej práci s dátumami.

Generovanie kalendára pre mesiac

Využitie dátumových funkcií umožňuje automaticky vytvoriť celý mesiac a mať tak k dispozícii kalendár, čo je veľmi praktické pre hotely, cestovné kancelárie, HR agentúry atď. Máme zadaný rok a mesiac a potrebujeme vytvoriť jednotlivé dni daného mesiaca. Pomocou funkcie DATE a vhodných vzorcov môžeme automaticky generovať dátumy pre celý mesiac. Môžete doplniť o funkciu KDYŽ (IF) a v mesiacoch, ktoré nemajú 31 dní, sa vám neobjaví prvé dni mesiaca nasledujúceho, čo zaisťuje presnosť a čistotu zobrazenia kalendára.

Práca s týždňami: Funkcie WEEKNUM a ISOWEEKNUM

Pri plánovaní a medzinárodnej komunikácii je dôležité vedieť určiť číslo týždňa v roku. Excel ponúka dve funkcie pre tento účel:

• WEEKNUM (ČÍSLO.TÝŽDŇA): Vráti poradové číslo týždňa dátumu v odkazovanej bunke.
• ISOWEEKNUM (ČÍSLO.TÝŽDŇA.ISO): 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 ISO 8601.

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. Medzinárodná štandardizačná organizácia zaviedla koncom 80-tych rokov definíciu ISO 8601.

Prečo je to dôležité? Predstavte si, že 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). Používanie ISOWEEKNUM zabezpečuje konzistentnosť v medzinárodnom obchode a komunikácii.

Výpočet pracovných dní a sviatkov: NETWORKDAYS a NETWORKDAYS.INTL

Často je potrebné vypočítať počet pracovných dní medzi dvoma dátumami, pričom sa ignorujú víkendy a sviatky.

• NETWORKDAYS (POČET.PRACOVNÝCH.DNÍ): Vypočíta počet pracovných dní medzi počiatočným a koncovým dátumom vrátane týchto dvoch dátumov. Preskočí nepracovné dni, a to vždy soboty a nedele a prípadne aj sviatky. Kedy sú sviatky (prípadne aj iné nepracovné dni - napr. celozávodná dovolenka) je nutné funkcii povedať.

  • Syntax: =NETWORKDAYS(začiatok; koniec; [sviatky])
  • začiatok: Počiatočný dátum.
  • koniec: Koncový dátum.
  • [sviatky]: Nepovinný argument. Oblasť s dátumami označujúca ďalšie nepracovné dni - napr. sviatky.Počet celých pracovných dní medzi dvoma dátumami v rozsahu A2 a A3, čo je 107. Do pracovných dní sa nezapočítavajú víkendy a dni voľna.

• NETWORKDAYS.INTL (POČET.PRACOVNÝCH.DNÍ.MEDZI.DNAMI): Vypočíta počet pracovných dní medzi počiatočným a koncovým dátumom vrátane týchto dvoch dátumov. Preskočí nepracovné dni - ako nepracovné dni je možné určiť konkrétne dni v týždni (nielen soboty a nedele) a prípadne aj sviatky.

  • Syntax: =NETWORKDAYS.INTL(začiatok; koniec; [víkend]; [sviatky])
  • [víkend]: Parameter určujúci, ktoré dni budú chápané ako nepracovné (predvolené = 1), napr. soboty a nedele (1), iba nedele (11) a pod. Možný je aj parameter napr. vo tvare „0000111“ znamenajúci, že ako víkend budú brané dni od piatku do nedele (číslovky 1) a ako pracovné dni budú brané dni od pondelka do štvrtku (číslovky 0).

Výpočet dátumu po určitom počte pracovných dní: WORKDAY a WORKDAY.INTL

Niekedy potrebujeme určiť dátum, ktorý nastane po uplynutí určitého počtu pracovných dní.

• WORKDAY (PRAC.DEŇ): Vypočíta dátum nasledujúci po uplynutí zadaného počtu pracovných dní od počiatočného dátumu. Preskočí nepracovné dni, a to vždy soboty a nedele a prípadne aj sviatky. Funkcia svojim použitím nenastaví dátumový formát, takže výsledkom bude poradové číslo (počet dní od 1.1.1900), formát je potrebné nastaviť ručne. Funkcia WORKDAY pracuje s krajnými dňami obdobne ako funkcia DAYS - t.j. jeden zo dní nie je považovaný za súčasť intervalu (t.j. keď napr. začneme pracovať v pondelok a pracujeme tri dni, tak uvedie ako výsledok štvrtok - ak chceme uviesť stredu, tak musíme od výsledku odčítať číslo 1) - líši sa v tom od funkcie NETWORKDAYS.

  • Syntax: =WORKDAY(začiatok; dni; [sviatky])

• WORKDAY.INTL (PRAC.DEŇ.MEDZI.DNAMI): Vypočíta dátum nasledujúci po uplynutí zadaného počtu pracovných dní od počiatočného dátumu. Preskočí nepracovné dni - ako nepracovné dni je možné určiť konkrétne dni v týždni (nielen soboty a nedele) a prípadne aj sviatky. Funkcia svojim použitím nenastaví dátumový formát, takže výsledkom bude poradové číslo, formát je potrebné nastaviť ručne. Funkcia WORKDAY.INTL pracuje s krajnými dňami obdobne ako funkcia DAYS - t.j. jeden zo dní nie je považovaný za súčasť intervalu.

  • Syntax: =WORKDAY.INTL(začiatok; dni; [víkend]; [sviatky])

Funkcia DAYS (DNI): Výpočet dní medzi dvoma dátumami

Funkcia DAYS (DNI) vypočíta počet dní medzi dvoma dátumami. K výpočtu sa použije vzorec konec-začiatok, t.j. jeden z krajných dátumov sa nepočíta ako súčasť intervalu (viď príklad s 5.1.). Počet dní medzi týmito dvoma dátumami v rozsahu A2 a A3, čo je 570.

Funkcia DAYS360 (ROK360): Výpočet dní na základe 360-dňového roka

Funkcia DAYS360 (ROK360) vypočíta počet dní medzi dvoma dátumami na základe roka s 360 dňami. Rok s 360 dňami sa niekedy používa ako zjednodušenie napr. pri výpočte úrokov.

• Syntax: =ROK360(start; koniec; [metoda])
• start: Počiatočný dátum.
• koniec: Koncový dátum.
• [metoda]: Spôsob zaobchádzania s konečným dátumom, ak je 31. dňom v mesiaci (predvolené = NEPRAVDA). Pri voľbe NEPRAVDA (USA metóda NASD) sa deň započíta, pri voľbe PRAVDA (európska metóda) sa nezapočíta (ako by mal daný mesiac 30 dní).
• Príklad: =ROK360(A1; A2) vypočíta počet dní od dátumu v bunke A1 do dátumu v bunke A2.
• Príklad: =ROK360(A4; A5; PRAVDA) vypočíta počet dní od dátumu v bunke A4 do dátumu v bunke A5, aj keď je v bunke A5 31. deň v mesiaci, spracuje ho ako 30.

Funkcie EDATE a EOMONTH: Posúvanie dátumov o mesiace

Tieto funkcie sú užitočné pre prácu s dátumami v rámci mesiacov.

• EDATE (EDATUM): Posunie dátum o zadaný počet mesiacov dopredu či dozadu tak, aby sa nezmenil deň v mesiaci, t.j. napr. z 15.1. sa stane 15.2., 15.3. a pod. Ak by malo vzniknúť neexistujúce dátum (napr. 31.2.), funkcia vráti posledný deň v danom mesiaci (28.2. alebo 29.2.). Funkcia EDATE je často používaná v bankovníctve, poisťovníctve a ďalších odvetviach, kde je potrebné počítať s budúcimi alebo minulými dátumami splatnosti alebo výročia.
• EOMONTH (KONIEC.MESIACA): Vypočíta dátum posledného dňa v mesiaci, ktorý je o zadaný počet mesiacov dopredu či dozadu od počiatočného dátumu. Je to ideálna funkcia, ak potrebujete zistiť koniec aktuálneho, minulého alebo budúceho mesiaca pre účtovné uzávierky, fakturáciu alebo iné procesy.

Praktické scenáre a doplňujúce informácie

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.Namiesto dátumu narodenia do nej vpíšeme alebo skopírujeme z inej tabuľky dátumy nástupu pracovníkov do zamestnania. 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). 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.

Autor tohto webu sa Microsoft Office (Word, Excel, Google tabuľky, PowerPoint) venuje od roku 2000 (od apríla roku 2004 na tejto doméne) a v roku 2017 od Microsoft získal prestížne ocenenie MVP (zatiaľ 8x za sebou). Svoje dlhoročné skúsenosti a know-how z používania rôznych počítačových programov pretavil aj do tohto článku. Svoje vedomosti a skúsenosti dáva k dispozícii aj on-line vo videách pre SEDUO. Vo firmách školí a konzultuje, učí na MUNI. Tento web už tvorí cez 20 rokov (o Excel píše cez 25).Páčil sa vám článok? Pomohol vám článok? Vyriešili ste problém? Môžete podporiť autora zakúpením tabuľky (samozrejme čokoládovej), keďže kávu nepije. Odkaz na zakúpenie čokolády je k dispozícii. Ak ste narazili v článku na nejasnosť, chybu alebo máte tip na vylepšenie či doplnenie článku, neváhajte sa ozvať.

tags: #excel #dni #po #narodeni

Populárne príspevky:

• Ako pripraviť dieťa na odber krvi nalačno?
• Komplexné informácie o pôrodnici v Rimavskej Sobote
• Mozaika Múdrosti, Lásky a Životných Pravd
• viac o pozornosti a jej poruchách
• Komplexný sprievodca inkontinenciou u mužov