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.
- =RANDBETWEEN(1;DNES())
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)))
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
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
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.
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”)