Výpočty rozdílů kalendářních dat v Excelu

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

Autor: Miroslav Lorenc

Excel počítá s kalendářními daty jako s čísly, a to v rozsahu od 1. 1. 1900 do 31. 12. 9999, kde každý den je reprezentován jako jedna jednotka. Díky tomu můžeme provádět výpočty s kalendářními daty jako s čísly. V následujících příkladech si ukážeme několik způsobů, jak vypočítat rozdíl mezi dvěma daty - např. k výpočtu věku zaměstnance, počtu pracovních dní určitého období nebo kvůli účetním odpisům.

Dnešní datum

Pokud potřebujeme v Excelu zadat aktuální datum, můžeme použít klávesové zkratky CTRL+ ; (Ctrl středník). (Pokud chceme, aby se datum automaticky aktualizoval, můžeme použít funkci DNES (TODAY), která nemá žádný argument.

  • =DNES()

Datum narození

Počátek období, které chceme počítat - např. datum narození - zadáme ručně. Pro náš demonstrativní příklad generujeme čísla náhodně v rozsahu 1 (tj. 1. 1. 1900) až aktuální datum.

Počet let

Pro výpočet celých let (bez uvádění částí roku) se hodí funkce DATEDIF. 

  • =DATEDIF(B3;B2;"Y")

Počet let a a jeho části můžeme vypočítat pomocí funkce YEARFRAC.

  • =YEARFRAC(B3;B2;1)

Počet celých měsíců (bez let)

Pro výpočet měsíců mezi daty jde použít více způsobů výpočtů, nejjednodušší je použití výše zmíněné funkce DATEDIF.

  • =DATEDIF(B3;B2;"YM")

Počet dní (bez let a měsíců)

Obdobně u výpočtu rozdílu počtu dní mezi dvěma daty bez let a měsíců.

  • =DATEDIF(B3;B2;"MD")

Počet dní

Výpočet rozdílu v kalendářních datech v jednotkách dní je asi nejjednodušší - počítáme prostý rozdíl mezi dvěma čísly. To můžeme udělat opět pomocí funkce DATEDIF,...

  • =DATEDIF(B3;B2;"D")

nebo pomocí funkce DAYS,...

  • =DAYS(B2;B3)

… ale nejjednodušší je použít prostý operátor pro odčítání - tj. mínus

  • =B2-B3

Počet pracovních dní

Pro výpočet pracovních dní mezi dvěma daty disponuje Excel funkcí NETWORKDAYS. Aby byl výpočet přesný, museli bychom ještě výpočet doplnit o výčet svátků v daném období.

  • =NETWORKDAYS(B3;B2)

Počet měsíců

Pro zjištění počtu měsíců v daném období můžeme použít funkci DATEDIFF.

  • =DATEDIF(B3;B2;"M")

Počet týdnů

Pokud bychom potřebovali znát počet týdnů v daném období, stačí použít některý z výpočtů pro zjištění počtu dní a výsledek vydělit sedmi.

  • =(B2-B3)/7

Počet let, měsíců a dní slovy

Trochu komplikovanější je sestrojení vzorce pro uvedení počtu let, měsíců a dní slovy. Jednak musíme počítat s případy, kdy nebude výpočet obsahovat všechny části - např. v našem demonstrativním příkladu není obsažen měsíc, protože počátek i konec období jsou obě lednová - tj. 46 let, 19 dní.. Zadruhé tu máme skloňování v češtině. Pro snadnější konstrukci výpočtu si vytvoříme pomocnou tabulku, ve které budeme pomocí funkce SVYHLEDAT doplňovat příslušný tvar slova na základě předešlého výpočtu.

Výpočet se skládá ze tří částí:

ROK - pomocí funkce KDYŽ zjišťujeme, jestli je počet let nenulový, pokud ano, tak spojíme výsledné číslo s mezerou a pomocí SVYHLEDAT připojíme text “rok” (pro číslo 1) nebo “roky” (pro číslo 2 až 4) a pro pět a více let připojujeme “let”. Dále připojujeme čárku jako oddělovač ve větě v případě, že je měsíc a rok nenulová hodnota.

MĚSÍC - opět pomocí KDYŽ zjišťujeme jestli je měsíc nenulový, připojíme mezeru a tvar slova měsíc a případně čárku, pokud je DEN nenulový.

DEN -  obdobně jako v případech výše, jen už neřešíme čárku na konci.

  • =KDYŽ(B4=0;"";B4&" "&SVYHLEDAT(B4;E2:H4;2;1)&KDYŽ(A(B6=0;B7=0);"";", ")&
    KDYŽ(B6=0;"";B6&" "&SVYHLEDAT(B6;E2:H4;3;1))&KDYŽ(B6=0;"";KDYŽ(B7=0;"";", "))&
    KDYŽ(B7=0;"";B7&" "&SVYHLEDAT(B7;E2:H4;4;1)))

Zdrojový soubor ke stažení

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

3 Komentářů

  1. Jak odečítat data narození a úmrtí u předků v rodokmenu, prosím (funkce yearfrac funguje jen od 1.1.1900), ale např. *2.2.1785 †14.5.1842?? děkuji za pomoc

  2. No teoreticky to počítat můžete, ale musela byste se nějak vypořádat s přechodem z juliánského na gregoriánský kalendářní systém. Ten skok o 10 dní nastal v různých částech země v odlišnou dobu – někde už v roce 1582, jinde se s tím vyrovnávali až později (viz VŘSR). V ČR
    se jedná o období 1582-1588. U každé osoby by tak bylo potřeba zohlednit v jakém systému se narodila a zemřela (dle místa narození a umrtí). V Excelu počítají všechny funkce až s daty od roku 1900. Ale vypočítat to jde.

  3. Kdesi jsem našel tento vzorec. Pokud máte datum narození v A2 a datum smrti B2, mohlo by fugovat toto. Ale je to teda trochu masakr a, jak píše kolega, kdoví jak je to tam s tím posunem kalendářů – takže spíš přibližně a za správnost neručím.
    =IF(DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”y”)=0;””;DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”y”)&” Year,”)&” “&IF(IF(DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”MD”)>=0;DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”YM”);DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”YM”)-1)=0;””;IF(DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”MD”)>=0;DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”YM”);DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”YM”)-1)&” Month,”)&” “&IF(IF(DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”MD”)>=0;DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”MD”);DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”MD”)+31)=0;””;IF(DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”MD”)>=0;DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”MD”);DATEDIF(DATE(IF(ISNUMBER(A2);RIGHT(YEAR(A2);4);TRIM(RIGHT(SUBSTITUTE(A2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(A2);MONTH(A2);TRIM(MID(SUBSTITUTE(A2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(A2);DAY(A2);LEFT(A2;SEARCH(“/”;A2;1)-1)));DATE(IF(ISNUMBER(B2);RIGHT(YEAR(B2);4);TRIM(RIGHT(SUBSTITUTE(B2;”/”;REPT(” “;99));99)))+7000;IF(ISNUMBER(B2);MONTH(B2);TRIM(MID(SUBSTITUTE(B2;”/”;REPT(” “;99));100;99)));IF(ISNUMBER(B2);DAY(B2);LEFT(B2;SEARCH(“/”;B2;1)-1)));”MD”)+31)&” Day”)

Napsat komentář

Vaše emailová adresa nebude publikována.

CAPTCHA

*

smazat formulářOdeslat komentář