Datum a čas - funkce prakticky

Úvodem aneb co se v článku dozvíte

Cílem tohoto článku je na praktických ukázkách předvést použití funkci datum a čas na praktických příkladech (většinou se jedná o dotazy, které jsem zodpovídal). Pro přehlednost je článek rozdělen do kapitol:

• Trocha teorie - aneb o funkcích datum a čas
  • Jak z čísla vytvořit datum
  • Jak z desetiného čísla vytvořit čas
  • Vlastní formát datum, čas
  • Klávesové zkratky - na vložení času datumu
• Získat/vytvořit z datum
  • Aktuální datum čas
  • Získat velikonočního pondělí
  • Pořadové číslo dne, měsíce, ...
  • Vytvořit datum z čísel
  • Poslední den v měsíci
  • Datum z pořadového dne v roce
• Vypočet z datum
  • Z času vypočíst mzdu - výpočty mzdy na základě odpracovaných hodin (výplatní lístek, faktura)
  • V kterých letech porobíhal projekt - zadán je začátak a konec projektu
• Počty z datum
  • Počet pondělku v měsíci - nebo jiných dnů (středy, čtvrtek, pátek)
  • Počet pondělku mezi dvěma daty- nebo jiných dnů
  • Počet dnů, měsíců - mezi dvěma daty - kolik dnů (měsíců) jste na světě (zadat jen datum narození)?
  • Počet dnů v měsíci - kolik jich je třeba v únoru?
  • Jak dlouho probíhal projekt? - dnů, hodin, minut
  • Jak určit čtvrtletí
• Čas a hrátky s časem
  • Čas na desetiné číslo - 6:30 na 6,5
  • Čas z desetiného čísla - 6,5 na 6:30
  • Zaokrouhlovat čas (například čtvrt hodiny) - 6:12 na 6:15
  • Kolik času zbývá - častý dotaz studentů
  • Jak dlouho trvalo? - kolik hodin mezi dvěma časy - například pro docházku
• Ostatní
  • Velká písmena dnů měsíců - automaticky
  • Průměrná rychlost - znám vzdálenost v (km) a čas (hod:min)
  • Java formát převod do Excel - jak získat excelovský datum
  • Formát času nad 24 hodin - zobrazit třeba 27 hodin
• Ke stažení
  • Ke stažení - ukázkové příklady zdarma

Trocha teorie úvodem

Když profesionálně řešíte v Microsoft Excelu rozsáhlejší (někdy i méně rozsáhlé ) úkoly ekonomického rázu, neobejdete se bez informace o datu a čase (hypotéky, kalendáře, docházky, kniha jízd, atd.).

Microsoft Excel nabízí množství funkcí pro práci s datem a časem, jen musíme vědět jak je využít. Jelikož mívám opakující se dotazy na toto téma, rozhodl jsem se publikovat tento článek. Snad Vám usnadní orientací v této problematice ukázkové praktické příklady.

Předpokládané znalosti

Pro pochopení článku doporučuji znalost syntaxe funkcí datum a čas a jak formátovat buňku. Obojí je přehledně sepsáno v mých článcích:

• Datum a čas - přehled funkcí - Microsoft Excel
• Jak natavit formát buňky - datum a čas

Poznámka: Můžete pokračovat dále bez znalosti, když praktické příklady nepochopíte můžete se k článkům vrátit.

Datum a čas je prosté číslo

Excel používá sytém 1900 nebo 1904. Přednastavený je systém 1900. Proto se v tomto článku budu zabývát tímto systémem. Princip je stejné jen pro 1904 je první den posunt.

Takže v systému 1900 je číslu 1 přiřazeno datum 1.1.1900. číslu 2 je přiřazeno datum 2.1.1900, atd. až do 31.12.9999 což je číslo 2.958.465. Poznámka: Excel starších verzí než 2003 možná neumí plný rozsah, ale předpokládám, že nikdo nepoužívá starší verze.

Podobně to je s časem, tomu odpovídá desetinné číslo 0,000 - 1,000. Takže 12:00 hod odpovídá 0,5. Jedné minutě odpovídá 1/(24*60). 24 hodin po 60 minutách. Jedné vteřině odpovídá 1/(24*60*60).

Bohužel Excel neumí se standartími funkcemi pracovat s datem před rokem 1900. Takže pro datum 12.12.1212 nelze zjistit zda bylo pondělí nebo středa. Existují sice VBA aplikace, které s tímto pomohou, ale je potřeba je do Excelu nainstalovat.

Jak z čísla vytvořit datum

Jak změnit formát jsme popsal v článku Formáty pro datum a čas v MS Excelu

Excel umí automaticky rozpoznat datum. Napíšemeli 1.1.1900 automaticky zapíše 1 a změní formát buňky na "datum a čas".

Zápisy, které Excel převede na datum.

• 12.2.2011
• 12/2/2011
• 12-2-2011
• 12-2/2011
• 12.únor.2011
• 12-únor-2011
• 12.2.11
• 12.2.
• 12-2
• 12/2
• 12.únor

Poznámka: Chceme-li mít datum zapsán jako text a ne číslo s formátem datum a čas. Je nutno buňce přiřadit formát text. Nevýhodou je, že již nebudeme moci matematicky pracovat s tímto datem.

Jak z desetiného čísla vytvořit čas

Excel umí automaticky rozpoznat čas. Napíšemeli 12:00 automaticky zapíše 0,5 a změní formát buňky na "datum a čas".

Zápisy, které Excel převede na čas:

• 12:12:12
• 11:11:11 AM
• 3:30 PM
• 15:30

Poznámka: Chceme-li mít čas zapsán jako text a ne číslo s formátem datum a čas. Je nutno buňce přiřadit formát text. Nevýhodou je, že již nebudeme moci matematicky pracovat s tímto časem. AM - dopolende. PM - odpoledne.

Zajímavost: Pokud vložíte čas 88:00:12, Excel tento čas převede na příslušný počet dnů. Největší čas který přepočítá je 9999:59:59 větší čas poté Excel považuje za text.

Vlastní formát datum, čas

Excel sice přiřazuje formát datumu a času automaticky, ale to můžete změnit. Nastavením vlastního formátu v dialogovém okně Formát buněk

Dialogové okno Formát buněk je k dispozici přes:

• Pravé tlačítko myší - klik na buňce a poté Formát buněk
• Karta Domů sekce Číslo čtvereček se šipkou.

Následně vybrat záložku číslo

Datum čas znaky

Formát data

Pro formát data se používají:

• d,dd,ddd,dddd
• m,mm,mmm,mmmm měsíce jako římské číslice (I až XII) - problém v české verzi se zobrazením Římských číslic
• rr,rrrr - v anglickou (EN) verzi Excelu yy, yyyy
• h,hh
• m,mm - minuty bohužel mají stejný název jako měsíce
• s,ss

Případně využít další možnosti (složené závorky, ...)

• h:mm:ss.00 - zlomky vteřin
• [mm] - uplynulý čas v hodinách
• [h] - uplynulý čas v minutách
• h:mm dop./odp. - zlomky vteřin

Poznámka

Pokud zadáte formát [h] při sečtení 20 hodin + 20 hodin bude výsledek 40 hodin. Při klasické h bude výsledek plus den a 16 hodin. Podobně funguje i u minut (sekund).

Problémem je zadávání římských čísel měsíců přes formát. Excel 2007 a 2010 má totiž na WINDOWS 7 problém s formátem mmm, proto je potřeba využít funkce ROMAN.

Více o vlastním formátu v samostatném článku: Jak na vlastní formát v Excelu.

Klávesové zkratky pro vkládání

Budete-li vkládat aktuální datum, čas, nemusíte jej vypisovat. Stačí použít klávesovou zkratku:

• Ctrl + ; – vloží aktuální datum podobně jako funkce =DNES()
• Ctrl + Shift + : – aktuální čas

Pokud budete chtít, aby došlo k automatické změně na aktuální datum, čas při přepočtu listu je notno použít fukce:

=DNES() =NYNÍ()

Aktuální datum čas

Jednou z důležitých vlastností co Excel umí je, že zobrazí aktuální čas. Hodí se například pro faktury (víme, kdy se tiskly) či pro cestovní zprávy (omezíme zneužívání, dokument bude mít aktuální datum a čas tisku. Zobrazení aktuálního času:

• =DNES() – zobrazí pouze aktuální datum
• =NYNÍ() – zobrazí aktuální datum s časem

=DNES() =NYNÍ()

Poznámka: Co je v buňce zobrazeno lze změnit formátem buňky.

Kolik času zbývá?

Při plánování složitějších úloh je vhodné vědět kolik času nám do odevzdání zbývá. Stačí k tomu jednoduchá funkce (vzorec):

• =ROK360(DNES();"27.9.2027") - výsledkem počet zbývajících dnů

Kde argumenty znamenají:

• DNES() - aktuální datum (dnešní)
• "27.9.2027" - od tohoto data se odečítá
• Výsledek - je počet zbývajících dnů

Pořadové číslo dne, měsíce, ...

DEN

Následující ukázkové příklady řeknou pořadové číslo dne, který je uveden ve funkci:

• =DEN("15-Dub-1993") - výsledek - 15
• =DEN("8.11.93") - výsledek - 8
• =DEN("8/11/93") - výsledek - 8

TÝDEN

Syntaxe je u tohoto trošinku složitější:

• DENTÝDNE(datum;typ)

Kde argument znamená:

• Datum opět se uvede nějaké datum
• Typ
  • 1 - nebo neuvedeno. Čísla od 1 (neděle) do 7 (sobota).
  • 2 - Čísla od 1 (pondělí) do 7 (neděle).
  • 3 - Čísla od 0 (pondělí) do 6 (neděle).

Příklad: =DENTÝDNE("12.12.12") - výsledek - 4

MĚSÍC

Podobně jako předchozí vrátí pořadové číslo měsíce =MĚSÍC("12.12.12")

Podobně pro minutu MINUTA(čas), rok ROK(datum)

Počet dnů měsíců mezi dvěma daty

C3 - např. datum vašeho narození

C4 - např. dnešní datum

• =DATEDIF(C3;C4;"y") - počet let
• =DATEDIF(C3;C4;"m") - počet měsíců
• =DATEDIF(C3;C4;"d") - počet dnů

POZOR: C4 > C3

=DATEDIF($D$7;$D$6;"y") =DATEDIF($D$7;$D$6;"m") =DATEDIF($D$7;$D$6;"d")

Poslední den v měsíci

V tomto měsící, pomůžeme si funkci DNES(), funguje i s funkci NYNÍ()

=EOMONTH(DNES();0)

V měsíci kterého den zadáme, klidně se můžeme odkazat do buňky ve kterém máte datum (např. B1)

=EOMONTH("6.2.2016";0)

Poslední den v příštím měsíci.

=EOMONTH("6.2.2016";1)

Datum z pořadového dne v roce

Jak z pořadového čísla dne v daném roze získat daný datum ve formátu den.měsíc.rok

=DATUM(2016;1; pořadové číslo dne ) =DATUM(2016;1;1) ... bude 1.1.2016 =DATUM(2016;1;32) ... bude 1.2.2016

Jak dlouho trvalo?

Další častý dotaz. Jak spočítat počet hodin. Využití například v docházkovém listu. Zadá se příchod a odchod pracovníka a Excel dopočte pročet odpracovaných hodin. Maximální délka 24 hodin. Předpoklad - nikdo nemlže odpracovat v jenom dnu více než 24 hodin.

Počet hodin - 1 den

Jednoduchý odečet.

=C10-B10

Nastavení formátu:

• číslo - zobrazí desetinné číslo (tak jak je fyzicky uložen čas v Microsoft Excelu)
• h:mm
• [m] - celkový počet minut

Počet hodin - přes půlnoc

=(C25-B25+(C25 < B25))

Nebo na první pohled možná jasnější:

=KDYŽ(C25 < B25;1-B25+C25;C25-B25)

Počet hodin - včetně přestávky

=( E42-B42+(E42<B42) - (D42-C42+(D42<C42)) )

Z času vypočíst mzdu (plat)

Jak na výpočty mzdy na základě odpracovaných hodin? Pokud nevíte, dozvíte se v této kapitole.

Příklad 1

Jak z časových údajů vypočíst peníze. Z předchozí kapitoly umíte zjistit čas strávený práci v daném dnu (0 - 23:59:59 hod). V tomto ukázkovém příkladu máte již k dispozici hodinovou sazbu a potřebujete vypočíst mzdu (plat) za odpracované hodiny.

Využijeme funkce MINUTA, HODINA.

=HODINA(B11) =MINUTA(B11)/60

Poznámka : Hodinová mzda je zadána v každé buňce. V případě možností může být hodinová sazba zadána v jedné jediné buňce a odkazovat se na tuto buňku.

Poznámka 2: Příklad 2 je jen rozšíření Příkladu 1. Viz soubor ke stažení zdarma.

Příklad 3

Jak na výpočet pokud, máte k dispozici celkové odpracované hodiny (například za celý týden, měsíc). Opět to není nic těžkého. Stačí použít funkce HODNOTA.NA.TEXT a MINUTA:

=HODNOTA.NA.TEXT(B28;"[h]")+MINUTA(B28)/60

Příklad 4

Potřebujete-li z hodnot v buňce vytvořit text (např. do výplatního lístku, faktury za služby, vykonanou práci). Můžete využít funkci CONCATENATE:

=CONCATENATE("Za odpracovaný ";C33; " hodin Vám naleží: ";E33;" ,- Kč")

Ke stažení zdarma

Soubor Jak z času vypočíst plat/mzdu. ke stažení zdarma pro Excel 2010 (2007).

Soubor Jak z času vypočíst peníze (plat/mzdu). ke stažení zdarma pro Excel 2003 (97).

Poznámka: Jeden ze zajímavých dotazů, proto jsem jej zpracoval do jedné kapitoly v článku ať není zbytečně schován v komentářích. Z dotazů vím, že komentáře se moc nečtou (obsahují odpověď na kladený dotaz).

Zpět na seznam kapitol.

Vytvořit datum z čísel

Vytvořit datum z čísel

• =DATUM(2007;12;10) - výsledek - 39246

Toho využijeme u speciálních složitějších funkcí, případně až budeme programovat ve VBA.

Vypočet velikonočního pondělí

Poznámka nejde o můj vzorec, našel jsem jej na internetu. Bohužel nikdo neuvádí autora, který je asi neznámý. Stačí zadat do google "Excel velikoneční pondělí" a najdete desítky různých odkazů.

Do buňky A1 zadat rok a nádledující vzorec zadejte do buňky ve které chcete mít velikonoční pondělí:

=(KČ(("4/"&A1)/7+MOD(19*MOD(A1;19)-7;30)*14%;)*7-6)+1

Poznámka: Pokud vám vychází velké číslo, je třeba změnit formát buňky.

Počet pondělku (i jiných dnů) v měsíci nebo mezi dvěma daty

V komentářích byl zajímavý dotaz: "jak pouzit funkci napr. k tomu kolik je úterků v daném měsíci, nebo v rozpětí dvou datumů, popř. jiných dnů jako čtvrtků, sobot". Jelikož se jedná o zajímavý dotaz, doplnil jsme článek

Počet pondělků (jiných dnů) v zadaném měsíci

=CELÁ.ČÁST((EOMONTH(C10;0)-C11)/7)-CELÁ.ČÁST((C10-C11)/7)+CELÁ.ČÁST(DENTÝDNE(C10-C11)/7)

• C10 ... první den v daném měsíci
• C11 ... hledaný den (2...Pondělí)

Počet pondělků (jiných dnů) mezi dvěma daty

=CELÁ.ČÁST((DENTÝDNE(C4-C6)-C4+C5)/7)

• C4 ... první den
• C5 ... poslední den
• C6 ... hledaný den (2...Pondělí)

Počet dnů v měsíci

V A1 je jakýkoli den v měsící ve kterém hledáme počet dnů:

=DEN(EOMONTH(A1;0))

pro starší verze Excel (bez funkce EOMONTH)

=DEN(DATUM(ROK(A1);MĚSÍC(A1)+1;))

Kód není z mé hlavy, ale nevím kde jsem jej získal.

Velká písmena dnů měsíců

Jak zajistit automaticky velká písmena názvů měsíců a dnů? Při použití formátu umí vloží Excel jen malé. Stačí použít funkci VELKÁ2 a HODNOTA.NA.TEXT a je hotovo. Případně první jedno (tři) písmena.

=VELKÁ2(HODNOTA.NA.TEXT(C13;"mmmm")) =VELKÁ2(ZLEVA(HODNOTA.NA.TEXT(C13;"mmmm");3)) =VELKÁ2(ZLEVA(HODNOTA.NA.TEXT(C13;"mmmm");1)) =VELKÁ2(HODNOTA.NA.TEXT(C13;"ddd")) =VELKÁ2(HODNOTA.NA.TEXT(C13;"dddd"))

Čas na desetiné číslo

Jak převést čas na desetinné číslo. Například 6:30 na 6,5. Například využít vzorce:

=CELÁ.ČÁST(A1)*24+HODINA(A1)+ZAOKROUHLIT(MINUTA(A1)/60;2)

Čas z desetiného čísla

Jak vytvořit čas z desetinného čísla. Například potřebujete převést 6,5 na 6:30. Využít lze vzorec:

' pokud mám čas 6,5 (jako 6 minut a 30 sekund) ' Minuty,Sekundy / 1440 =6,5/1440 ' pokud mám čas 6,5 (jako 6 hoddin a 30 minut) ' Hodiny,Minuty / 24 =6,5/24

Zaokrouhlovat čas (například čtvrt hodiny)

Jak zaokrouhlovat čas na čtvrthodiny, tak ať je zachován formát času:

• 2:08 2:15
• 3:20 3:15

=MROUND(A1;"0:15")

Funguje i když potřebujete zaokrouhlovat na 10 minut ;) atd.

=MROUND(A1;"0:10")

V kterých letech porobíhal projekt

Je zadán začátek a konec projektu (který trvá několik let) a je potřeba umět tento projekt filtrovat zda v daném roce probíhal.

=KDYŽ(NEBO(ROK($C5)>E$4;ROK($D5)<E$4);0;1)

Soubor V kterých letech porobíhal projekt ke stažení zdarma.

Jak dlouho probíhal projekt?

Potřebujeme zjistit jak dlouho probíhal projekt, tj. kolik dnů, hodin a minut. V C1 je rozdíl mezi časem ukončení a časem začátku.

=CELÁ.ČÁST(C1) &" Dnů " & CELÁ.ČÁST(MOD(C1;CELÁ.ČÁST(C1))*24) & " hodin " & MINUTA(C1) & " Minut"

Průměrná rychlost

Znám ujetou vzdálenost v (km) buňka A1 a buňce B1 čas, který byl k překonání této vzdálenosti potřeba(hod:min). Řešení je jednoduché:

=A1/B1/24

Zjistit čtvrtletí

V buňce A1 je datum a potřebujeme zjisti, ve kterému čtvrtletí přísluší. Jedním z možných řešení je využít ROUNDUP:

=ROUNDUP(MĚSÍC(A1)/3;0)

nebo přes funkci VYHLEDAT

=VYHLEDAT(MĚSÍC(A1);{1;4;7;10};{1;2;3;4})

nebo s využitím CELÁ.ČÁST

=CELÁ.ČÁST((MĚSÍC(A1)-1)/3+1)

Java formát převod do Excel

Máte formát datmu typu 1079017149062 nebo 1420235100 a potřebujete získat datum v Excel formátu. pomoc je jednoduchá:

Pro verzi 1

=(A1/(3600*24*1000))+DATUM(1970;1;1)

Pro verzi 2

=(A1/(3600*24))+DATUM(1970;1;1)

Formát času nad 24 hodin

Je potřeba nastavit vlastní formát buněk na:

[h]:mm

Poznámka funguje i pro minuty

[mm]

Vlastní formát více

Více o vlastním formátování je popsáno v samostatném článku zde.

Ukázkové příklady - zdarma ke stažení

Soubor Datum a čas - praktické příklady Excel ke stažení zdarma.

Závěrem

Další příklady jsou v přípravě. Pokud Vás napadá nějaký zajímavý příklad, který lze řešit pomocí funkcí datum a čas můžete se zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 10:57

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.